SQL Server Questions Part II

Guys,

Today we will see some more generic questions on SQL Server.

Here are more questions:


Q. How can you ensure that a table named TableB with a field named Fld1 will only have those values in the Fld1 field that are also in the table named TableA with a field named Fld1?

Ans: Use referential integrity / Foreign Key Constraint

Q. How can you reset Identity key value of a table?

Ans: Truncate Table will reset the Identity value of table.

Q. What can be used to ensure that a field in a table only accepts a certain range of values?

Ans: We can use various constraints that can be used to achieve this.

There are various constraints available in SQL Server.

Primary Key Constraint => to uniquely identify a row without using NULL

Foreign Key Constraint => to maintain referential integrity between 1 or more tables

Unique Key Constraint => to uniquely identify a row

CHECK Constraint => the value entered is validated and if it fails condition then don't allow entry of data

Default Constraint => If we don't supply any value for field then set value of field with specified value

Null Constraint => Whether field can accept NULL value or not.

Q. Our company recently introduced a policy where all names of objects must follow naming convention. For ex. Table must start with tbl_, procedure should start with prc_ etc. How can I ensure all new objects must adher to these policies and if not followed object should not be created?

Ans: 1 way to use DDL triggers. From 2008 onwards we can also refer to Policy Based Management to ensure this.

Q. Is it possible to drop multiple objects using single DROP statement?

Ans:
Drop Table a,b,c
Drop proc a,b,c

Q. If you have a stored procedure, you want to just check the syntax of the same rather than executing it, what will u do?

Ans: Press CONTROL + F5 to check for its syntax. Or click on button next to EXECUTE button.

Q. Which event (CHECK constraint, Foreign Key, Rule, Trigger, Primary Key check) will be performed last for an integrity check?

Ans: Trigger because everything is executed before data is inserted in the table & as soon as data is entered in Table, trigger is fired.

Q. When a new parameter is added to an SP, what steps to take to ensure existing code in an application does not break?

ANS: Give a default value to the newly added parameter. This will ensure that all existing code will not fail.

Q. When we drop a variable length column using ALTER TABLE DROP COLUMN statement, the space used by the column is not automatically claimed. What will u do to claim the space?

Ans: DBCC CLEANTABLE (database_name, table_name)

Q. I have a table Employee with following definition:


Employee (
EmpID int Not Null Identity (1,1),
EmpName varchar(100) default 'test',
Age int default 2)

What query you will write to insert default values in EMPLOYEE table?

Ans: Insert into Employee Default Values

Q.How to retrieve a single table from database backup?

Ans: It is not possible to directly restore 1 table from backup. Only option is to restore the database somewhere else & then load data of required table and transfer to destination.

Q. How do you flip rows to columns and vice-versa?

Ans: We can use PIVOT, UnPivot to flip rows to columns & vice-versa.

Q. What is WITH ENCRYPTION clause and where it is used?

Ans: This will encrypt the definition of object and it will not be visible whenever we use sp_helptext. This can be done with View, Stored Procedure, Function etc

Q. What will happen if I write following statement:
Select getdate()
go 10

Ans: Select Getdate() is executed 10 times because GO 10 acts as a loop.

Q. What is the purpose of the USE command?

Ans: The USE command helps select any available database, so that every query is executed on selected database else default database is used.

Do let me know if you have any comments or more questions.
 
Thanks
 
Sudhir