Basic SQL Questions

Guys,

Today we will discuss some basic SQL questions.

Q. What is normalization ?
Ans: Normalization is the process of reducing the redundancy in data in the RDBMS.

Q. How to create a table structure and not data, from another table in the same database, in one query ?
Ans: Select * into table2 from table1 where 1 = 2Here we can give any where clause that doesnot return any rows.

Q. What is a database transaction ?
Ans: A database transaction is the smallest unit of work in a database.

Q. What is are properties of database transaction ?
Ans: A database transaction always comply with ACID properties -- Atomicity, Consistency, Isolation and Durability.

Q. What are the different Isolation levels ?
Ans: Read uncommitted, Read committed, Repeatable Read and Serializable.

Q. What is differnce between DELETE and TRUNCATE ?
Ans: DELETE is a logged operation. So, each row deleted is logged in the transaction log.TRUNCATE is not a logged operatio. So, each row is not entered in transaction log.The logging makes the DELETE slower than TRUNCATE.

Q. Compare UDFs and Stored procedure ?
Ans: UDFs can be used in select, where, join or case statements while SPs cannot be. UDFs cannot be used in DML statements to modify, while we can use them in stored procedures.

Q. When do you use cursor ?
Ans: If you have to process one row at a time and loop through a set of rows, you can use cursors.

Q. What are the special tables that can be used only within trigger ?
Ans: "Inserted" to check the rows inserted through the trigger and "Deleted" to check the rows deleted through the trigger.

Q. What is the difference between clustered and non-clustered index ?
Ans: Clustered index is a special kind of index in which the node of the B tree has the actual value. Clustered index physically sort data whereas Non-Clustered index sort data logically. In Clustered indexe leaf pages are equal to data pages whereas in Non-Clustered index leaf pages point to data pages.

Q. What is new in date time in sql server 2008 ?
Ans: In the previous versions of SQL server, date and time cannot be stored seperately. In 2008, there are 2 new seperate datatypes -- date and time.

Q. What are the new spatial datatypes in sql server 2008 ?
Ans: Geography and Geometry. Flat data -- such as planar is supported by the geometry data type. Round earth data, such as latitude and longitude, is supported by the geography data type.

Q. What is new in date time in sql server 2008 ?
Ans: In the previous versions of SQL server, date and time cannot be stored seperately. In 2008, there are 2 new seperate datatypes -- date and time.

Q. What is the new hierarchyid data type in sql server 2008 ?
Ans: Hierarchyid is new datatype introduced to easily store hierarchy data such as organisational structure.

Q. What is sparse column in sql server 2008 ?
Ans: A sparse column is a special type of column that has been optimized for columns that has lot of null values. It is recommended to declare a column sparse, if the column has or is expected to have more than 20% null.

Q. What is new in Filtered Indexes and Statistics in sql server 2008 ?
Ans: In 2008, we indicate filtered indexes and statistics on specific subset of rows, that are well defined, like the ones that has null.

Q. What is new in sql server 2008 TDE ?
Ans: There is a new option for the who database to be automatically encrypted using Transparent data encryption (TDE).

Q. What is new in backup in sql server 2008 ?
Ans: Backup Compression in sql server 2008 supports compressing the backups and storing at a less disk space.

Q. What is news in Audting in sql server 2008 ?
Ans: customized audits of database events can be created using SQL Server 2008 Audit functionality.

Q. What is Resource Governor in SQL Server 2008 ?
Ans: The Resource Governor is a tool given by SQL Server 2008 to control and allocate CPU and memory resources depending on the priority of applications.

Q. What is External key management in SQl Server 2008 ?
Ans: If there is a way to store key and data to be stored seperately on the encryption, it is the best way for security. External Key Management enables this and allows keys to be stored by third-party.

Q. What is Table-Valued Parameters in SQL Server 2008 ?
Ans: Table-Valued Parameters (TVPs) are the output or input parameters of stored procedures that can accept or return table as parameters rather than just one value at a time as in earlier versions of SQL Server.

Q. What is Large User Defined Type in SQL Server 2008 ?
Ans: In SQL Server 2008, large user-defined types (UDTs) can create custom user defined data type of size limit of 2 GB compared to the previous 8 KB.

Q. What is new in backup compression in SQL Server 2008 ?
Ans: Backup can be compressed in sql serevr 2008 without actually compressing the database itself. The is data automtically decompressed when data is restored.

Q. What is hot add CPUs in SQL Server 2008 ?
Ans: One of the new features in SQL Hot-add CPUs. This feature allows the addition of extra CPUs to the server without having to actually shutdown the server.

Q. What is special of Data encrytion in SQL Server 2008 ?
Ans: In SQL Server 2008, data can be encrypted and stored securely by Transparent Data Encryption

Q. Name one CLR improvement in SQL Server 2008.
Ans: Common Language Runtime (CLR) has table-valued functions. Now, to run the query more efficiently, an order clause can be used in the create function.

Q. What is spatial index in SQL Server 2008 ?
Ans: SQL Server 2008 introduced the support for spatial datatypes as new feature. The index that is created on a spatial column in the table is called spatial index.

Q. What is the maximum number of columns per select or insert statement in SQL Server 2008 ?
Ans: 4096

Q. What is the maximum Database size in SQL Server 2008 ?
Ans: 524,272 terabytes

Q. What is the maximum number of databases per instance of SQL Server 2008 ?
Ans: 32,767

Q. What is maximum number of non-clustered index per table in SQL Server 2008 ?
Ans: 999

Q. What is the maximum number of parameters in stored procedures or user defined functions in SQL Server 2008 ?
Ans: 2,100

Q. What is the nesting maximum for stored procedures/triggers/subqueries in SQL Server 2008.
Ans: 32

Q. What is the maximum bytes per row in SQL Server 2008 ?
Ans: 8,060

Q. What is use of sp_who ?
Ans: Currently running users and process in SQL Server can be found by running sp_who.

Q. What is sp_who2 ?
Ans: It shows little more that sp_who with locking, blocking, what the users logged in are doing and so on.

Q. What is instead of trigger in SQL Server ?
Ans: Instead of triggers -- are triggers attached to table in which, the code inside them is executed in place of the original insert/delete/update statement

Q. What is DBCC DBREINDEX in SQL Server 2008 ?
Ans: DBCC DBREINDEX is used to rebuilds indexes for a table. Please note that it is deprecated in SQL SERVER 2008 and will be removed in the next version. It will be replaced by REBUILD option in Alter Index.

Q. What is a Common Table Expression (CTE) ?
Ans:Common Table Expression can be thought of as temporary result set. But, it has lot of powers including recursive usage.

Example:
WITH MYCTE (ID, NumberOfOrders)
AS
(SELECT ID, COUNT(*)
FROM
Order
GROUP BY ID)
select * from MYCTE

Q. What is row_number() in SQL Server ?
Ans: row_number () returns the sequential number of a row in a table for all the rows. It also has a partition clause that can return sequential number starting fresh with every partition.

Q. What in newid() in SQL Server ?
Ans: NewID() creates a unique value. The type of value returned by newid is a uniqueidentifier.

Q. What are the difference between local and global temporary tables ?
Ans: Local tables are represented as #temp and global ones are represented as ##temp. Local temporary tables can been seen/accessed by only the user who creates it and are deleted when the user disconnects from the current instance of SQL Server. Global temporary tables can be seen/accessed by any user and are deleted only when all users referencing the table disconnect from the instance of SQL Server.

Q. What is a Control flow element in SSIS ?
Ans: Control flow element is one that performs any function or provide structure or control the flow of the elemtents. There must be at least one control flow element in the SSIS package.
What are report definition and client report definition files ?Ans: Report definition files with .rdl extensions are created when you create a report in report builder. It may contain data sources, queries, report layout, data and other necessary reporting needs.Client report definition files with .rdlc files are created when you create a report using Visual Studio Report Designer to be used with ReportViewer Control.

Q. What is a Rendered Report ?
Ans: Report Server process the published report into viewable output format which contains the necessary data and designed layout. The is called the rendered report which can be HTML or XML or any suitable format.

Q. What are the different types of reports that can be created by SSRS 2008 ?
Ans: Ad hoc reports, Cached reports, Clickthrough reports, Drilldown reports, Drillthrough reports, Linked reports, Parameterized reports, Snapshot reports and Subreports.
What is a data region in SSRS 2008 ?Ans: The region where the data is displayed from the dataset is the Data region and columns

Q. What is a Tablix ?
Ans: Tablix is a common term for supporting multiple rows and columns. The rows can be static or dynamic. Table, Matrix and list data regions are based on tablix.

Will post more later.

Happy SQL Coding.