SQL Server Interview Questions and Answers Part 4

23. What is an IDENTITY column in a database table?

Answer:

IDENTITY column is like an AutoNumber field in Microsoft Access or a sequence in Oracle.

Using Identity column, you make that column as Auto incremental number or a surrogate key.

24. How to write a query to get the list of triggers in a database?

Answer:

Select * from sys.objects where type='tr'

25. Explain the difference between Union and Union all.

Answer:

UNION

UNION combines the result set of two or more queries into a single result set. A UNION statement is like a SELECT DISTINCT on the results set.

In UNION, the number of columns and sequence of columns must be the same in all queries and the data types must be compatible.

UNION ALL

UNION ALL is very similar to UNION except it includes duplicate rows in the result set.

26.  What is dynamic SQL?

Answer:

Dynamic SQL is a set of statements dynamically constructed and executed at runtime and it will not be stored in the database.

27. What is the use of SET NOCOUNT ON/OFF statement?

Answer:

When SET NOCOUNT is OFF, the count is returned which indicates the number of rows affected by a Transact-SQL statement.

When SET NOCOUNT is ON, the count is not returned.

By default, NOCOUNT is set to OFF

When you don't want to display the number of records affected, set it explicitly ON.

28. Explain SUBSTR and CHARINDEX in the SQL Server.

Answer:

SUBSTR returns specific portion of string in a given string.

CHARINDEX returns character position in a given specified string.

SUBSTR("Cricket",3) -- returns Cri

CHARINDEX("Cricket",'i',1) -- returns 3 as 'i' appears in 3rd position of the string

29. What is ISNULL() operator?

Answer:

ISNULL function checks whether value given is NULL or not NULL in Sql Server.