SQL Server Interview Questions

What is DTS in SQL Server ?

Data Transformation Services is used to transfer the data from one source to our required destination. Considering am having some data in sql server and I need to transfer the data to Excel destination. Its highly possible with dialogue based tool called Data Transformation services. More customization can be achieved using SSIS. A specialized tool used to do such migration works.

What is the significance of NULL value and why should we avoid permitting null values?

Null means no entry has been made. It implies that the value is either unknown or undefined.We should avoid permitting null values because Column with NULL values can't have PRIMARY KEY constraints. Certain calculations can be inaccurate if NULL columns are involved.

Difference between primary key and Unique key?

• Both constraints will share a common property called uniqueness. The data in the column should be unique.
• The basic difference is,• Primary key won’t allow null value. Whereas, unique key will accept null value but only one null value.•
• On creating primary key, it will automatically format the data inturn creates clustered index on the table. Whereas, this characteristics is not associated with unique key.
• Only one primary key can be created for the table. Any number of Unique key can be created for the table.

What is normalization?

Normalization is the basic concept used in designing a database. Its nothing but, an advise given to the database to have minimal repetition of data, highly structured, highly secured, easy to retrieve. In high level definition, the Process of organizing data into tables is referred to as normalization. Denormalization:As the name indicates, it's opposite to normalisation. Introducing redundancy into the database is referred to as denormalization. The database becomes too complex and too many joins may occur to fetch the data. In that case, we used to prefer this denomalization concept. Narrow idea - Joining the divided or segregated tables.What is the difference between UNION ALL Statement and UNION ?The main difference between UNION ALL statement and UNION is UNION All statement is much faster than UNION,the reason behind this is that because UNION ALL statement does not look for duplicate rows, but on the other hand UNION statement does look for duplicate rows, whether or not they exist

What is a view?

If we have several tables in a db and we want to view only specific columns from specific tables we can go for views. It would also suffice the needs of security some times allowing specfic users to see only specific columns based on the permission that we can configure on the view. Views also reduce the effort that is required for writing queries to access specific columns every time.
What are the types of indexes available with SQL Server?

There are basically two types of indexes that we use with the SQL ServerClustered -

1. It will format the entire table, inturn physically sort the table.

2. Only one clustered index can be created for a table.

3. Data will be located in the leaf level.

4. By default, primary key will create clustered index on the table

.Non-Clustered Index

1. It wont touch the structure of the table.

2. It forms an index table as reference to the exact data.

3. A reference to the data will be located in the leaf level.

4. For a table, we can create 249 non clustered index

Stored Procedure Vs User Defined functions

in sql server Stored Procedure:Its a pre-compiled statements incorporating a set of T-SQL statements.We can't use SP in Joins.Stored procedure won't return table variables.User defined functions:Its a set of T-SQL statements. Repeated T-SQL statements can be grouped as a User defined functions.We can use UDF's and in joins.UDF returns table variablesCorrelated maximum capacity for SQL Server 2005:

Table Level:

• Maximum Number of tables : No restrictions (Until your harddisk size you can create tables)
• Maximum bytes per row in the table : 8060 bytes
• Maximum tables in a select statment - 256
• Maxiumu references per table - 253
• Maximum rows in the table - Unlimited (Depends on the storage)
• Maximum columns per base table : 1024
• Maximum columns per insert statment : 1024
• Maximum columns per Select statment : 1024 * 4 = 4096Bytes per Group by or Order by = 8000
  Index:
• Maximum clustered index per table - 1
• Maximum Non-clustered index per table - 249Maximum Columns in an index (composite index) - 16
  Keys:
• Maximum Columns in a Primary key - 16
• Maximum Columns in a Foreign key - 16
  Objects:
• Nested Stored procedure levels- 32
• Nested triggers levels- 32
• Parameters per stored procedure - 2100
• Parameters per User defined functions - 2100
• Identifier length - 16
  Database Level Details:
• Number of instances for a server - 50
• Files per database - 32767
• Filegroups per database - 32767
• Databases per instance of SQL Server – 32767

Delete VS Truncate:

• Delete will delete row by row in the table. The syntax is,delete from tablenameWe can give where conditions for deleting the data. Each delete will be logged in the log file.Its a DML statement
• Trucate will deallocate the reference in the pages instead of deleting the data.Its DDL statement.
• The syntax is,Truncate table tablenameWe can provide truncate with tables having foreign key relationships. (Because it wont do any cascade delete on the table).It wont log any information in the log file.Delete or truncate the data at a stretch and wont do any row by row manipulation