:::: MENU ::::

Posts Categorized / T-SQL Interview Questions

  • Jan 07 / 2010
  • 0
CTE, dbDigger, T-SQL Interview Questions

Order of clauses in T-SQL

Question:

When writing a Common Table Expression, which is the proper order of the clauses? Assume the ORDER BY, GROUP BY, and SELECT clauses apply to the outer query, not the query used to define the CTE.

Answer: WITH, SELECT, GROUP BY, ORDER BY

Explanation: When creating a Common Table Expression, the WITH clause is first and precedes the SELECT clause of the query. GROUP BY, if needed, is before ORDER BY, in a SELECT query. Note that the CTE contains it’s own query inside parenthesis, all of which is a part of the WITH clause and there can be multiple queries for separate CTEs that are being built.

Note: The question of day is taken from SqlServerCentral.com. I want to compile selected QOD to share.

  • Jan 07 / 2010
  • 0
dbDigger, T-SQL Interview Questions

SET Options for a T-SQL query

Question:

In creating a query that will be used in Query Notification, how are the SET options determined for the query?

Answer: A specific group of SET options are required on the client.

Explanation: When building a query for query notification, a specific set of SET option setttings must be on the client’s connection. specifically they are:

  • ANSI_NULLS ON
  • ANSI_PADDING ON
  • ANSI_WARNINGS ON
  • CONCAT_NULL_YIELDS_NULL ON
  • QUOTED_IDENTIFIER ON
  • NUMERIC_ROUNDABORT OFF
  • ARITHABORT ON

Note:: The question of day is taken from SqlServerCentral.com. I want to compile selected QOD to share.

  • Jan 07 / 2010
  • 1
dbDigger, T-SQL Interview Questions, T-SQL Tips and Tricks

Interupting WAIT FOR in T-SQL

Question:

Which of the following items can be used to end a WAITFOR statement in SQL Server 2005?

Answer:

  • specific time
  • An elapsed period of time
  • A message in a Service Broker queue
  • A timeout

Explanation: All of these can actually end a WAITFOR statement. You can pick a particular time or an amount of time that elapses in the statement. You can also specify that the WAITFOR ends when a message is received in a Service Broker queue, or a timeout passes in waiting for that message.

Note: The question of day is taken from SqlServerCentral.com. I want to compile selected QOD to share.

  • Jan 07 / 2010
  • 0
Cursors, dbDigger, T-SQL Interview Questions

Get count of the number of rows in the cursor

Question:

If you open a cursor, which of these will allow you to get a count of the number of rows in the cursor? (select all that apply)

Answer:

  • sp_cursor_list
  • sp_decribe_cursor
  • sp_describe_cursor_columns
  • @@cursor_rows

Explanation: All of these items will allow you to determine the number of rows in some cursors, subject to restrictions. The @@Cursor_rows requires a non-dynamic cursor and the sp_describe_cursor_columns requires you to count the number of rows returned.

The question of day is taken from SqlServerCentral.com. I want to compile selected QOD to share.

  • Jan 07 / 2010
  • 1
dbDigger, T-SQL Interview Questions, T-SQL Scripts, T-SQL Tips and Tricks

How to delete duplicate rows from a table

A DBA may be often required to delete duplicate rows from a SQL Server database table. Several methods can be used keeping in view the resources and exact situation. Here I will create a specific scenario. After going through this scenario, you will be able to remove the duplicate rows in any given scenario on ground.
Run following script to generate duplicate data in a table named “dups” created in any database that you select. Here I will use “pubs”.

 
-- Create table in any database  
 create table Dups  
 (col1 varchar(50),  
 col2 varchar(50),  
 col3 varchar(50))  
 GO  
 -- Populate the table for duplicate rows  
 INSERT INTO dups  
 SELECT 'Val1','val2','Val3'  
 UNION ALL  
 SELECT 'Val1','Val2','Val13'  
 UNION ALL  
 SELECT 'Val1','Val2','Val3'  
 UNION ALL  
 SELECT 'Val1','Val2','Val13'  
 UNION ALL  
 SELECT 'Val11','Val12','Val3'  
 UNION ALL  
 SELECT 'Val16','Val18','Val20'  
 UNION ALL  
 SELECT 'Val11','Val12','Val3'  
 UNION ALL  
 SELECT 'Val16','Val18','Val20'  
 UNION ALL  
 SELECT 'Val6','Val7','Val6'  
 GO  

Table is created and populated with duplicate rows in it now.
Total rows are 9
Distinct rows are 5, which we have to preserve at the end.
Now we have to remove all duplicate rows, leaving behind just 5 distinct rows in three columns.

Execute the following script to complete the operation

 
-- Remove duplicate rows  
 ALTER TABLE dups  
 ADD ser SMALLINT PRIMARY KEY identity(1,1)  
 GO  
 DELETE FROM dups  
 WHERE ser not in (SELECT max(ser) FROM dups  
 GROUP BY col1,col2,col3)  
 GO  

At the end now we have all duplicate rows removed and our table is with an identity column “ser”. You may also drop this column by using

 -- Drop the temporary column created  
 ALTER TABLE dups  
 DROP COLUMN ser  
 GO  

but for that you have to first drop the constraint attached with it. As in my case it is as follows

 -- Drop PK constraint  
 ALTER TABLE dups  
 DROP PK__dups__286302EC  
 GO  

So it is over. You may get include any number of columns in your distinct rows wish list. You may modify the ‘group by’ parameters to meet any number of columns to consider in duplicate row . Query uses the magic of ‘group by.’ Here it is appropriate to mention that “distinct” and “group by” both produce same result and almost same resource utilization. But one of important difference between both is that group by makes you able to apply an aggregate function on each group of data separately. That fact has used here with “max” in our duplicate removal query.

Click here to read an excellent article on this topic by Arshad Ali. It covers different ways to deal with duplicate rows.

Consult us to explore the Databases. Contact us