:::: MENU ::::

Posts Categorized / T-SQL Interview Questions

  • Oct 02 / 2017
  • 0
T-SQL Enhancements, T-SQL Interview Questions, T-SQL Scripts, T-SQL Tips and Tricks

Exec vs Sp_ExecuteSQL for dynamic SQL execution

Exec and sp_executeSQL both are used for executing the dynamic sql statements. There are some differences between the both

EXEC

Exec is available since early versions before the sp_executesql. It has no direct support of using or storing the local variables. Also using Exec makes the setup vulnerable to sql injection attacks as parameters appear as part of code.

SP_ExecuteSQL

sp_executesql is improved way to execute dynamic sql statements. It provides mechanism to use and store values in local variables. This mechanism is not vulnerable to sql injection as variables do not appear as part of code but as an operand. Also it enables the sql engine to use the cached execution plan of the statement like a stored procedure.

Conclusion

sp_executesql has upper hand as compared to exec method of executing dynamic sql. Keeping in view the flexibility, efficiency and security of sp_executesql we should opt it for executing dynamic SQL.

  • Aug 29 / 2013
  • 0
Data Modeling and Database Design, dbDigger, Security and Permissions, T-SQL Interview Questions, T-SQL Tips and Tricks

Drawbacks of using Dynamic SQL

I am performing some knowledge discovery that what may be the bad effects of using dynamic SQL in SQL Server environment. So while going through some good articles on it i have figured out following major issues/side effects of dynamic SQL

  • Query plans of Dynamic SQL are not cached so not reused
  • Dynamic SQL can put you vulnerable for SQL injection attack
  • Messy quotation marks and  spacing complicate the query writing
  • Network traffic is increased as compared to a USP executed
  • Generating the dependencies through various methods becomes unreliable as objects used in dynamic SQL can not be traced by system views
  • Ownership chaining is skipped hence permissions are compromised

These major reasons are enough to think twice before using the dynamic SQL. So use it wisely and also look for options to avoid it.

  • Apr 11 / 2013
  • 0
dbDigger, ETL, T-SQL Interview Questions, T-SQL Tips and Tricks

Using TOP in DELETE statement for T-SQL

Suppose we have a WHILE loop for deleting specified amount of data batches in each loop. Simple solution is to use TOP operator in loop for specifying the number of rows to be deleted in each batch. But it generates error while trying to use TOP operator in delete operation. Let us have a look at the problem.

-- Create a table with only one column in it  
 CREATE TABLE DemoTable (id SMALLINT identity(1, 1))  
 GO  
 -- Insert 100 values in identity column  
 INSERT INTO DemoTable DEFAULT VALUES   
 GO 100  
 -- Verify the values   
 SELECT * FROM DemoTable  
 GO  
 -- Try to delete statement for top 10 rows  
 DELETE TOP 10 FROM DemoTable  

but following error message is generated

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ’10’.

It looks that TOP operator is not working in the DELETE statement. So can we use the TOP operator in DELETE operations or not?

Answer is YES, we may use TOP operator in DELETE operations but with little modification. Correct statement is as under with brackets around the number.

 -- Using TOP in delete statement  
 DELETE TOP (10) FROM DemoTable  

So we have deleted the 10 records from demo table by using TOP operator in DELETE statement. Similarly instead of constant number any variable may be used in brackets with TOP.

 -- Drop DemoTable  
 DROP TABLE DemoTable  

However it is notable that according to BOL
“TOP cannot be used together with UPDATE and DELETE statements on partitioned views.”.

  • Feb 07 / 2012
  • 0
Data Modeling and Database Design, DBA Interview questions, dbDigger, Identity Columns, SQL Server Error messages, T-SQL Interview Questions

Multiple identity columns specified for table %’. Only one identity column per table is allowed

Following error message would be generated if more than one identity column is tried to define in a table.
Multiple identity columns specified for table ‘%’. Only one identity column per table is allowed
SQL Server does not allow to have more than one identity column in a single table. Consider following design for generating error message.

 CREATE TABLE identTest  
 (ID1 SMALLINT IDENTITY(1,1),   
 ID2 SMALLINT IDENTITY(1,1))  
 GO  

There would be hardly any scenario when such a design is required. However such functionality may be achieved by a couple of work around

  • Use trigger to insert values in second column
  • Configure second column as computed column and reflect values from identity column
  • Feb 07 / 2012
  • 0
dbDigger, Identity Columns, T-SQL Interview Questions, T-SQL Tips and Tricks

Removing gaps in identity column values, duplicate value errors

I have got a query about gaps in values of identity column and error messages related to duplication in values of identity column. The problem may be divided in two parts Gaps in identity column values and Duplication error messages related to seed property. Let me discus these one by one

Gaps in identity column values

Gaps in values of identity columns are generated by failed inserts or successful delete operations on table. It has no considerable negative impact but may be removed for optimized usage of identity column data type. For example if there is an identity column with data type tinyint then after reaching value of 255 the column would not be able to hold further rows. Same is true for other identity data types like int, bigint, smallint, decimal or numeric. In such circumstances removing the gaps among values of identity column may optimize the usage of identity column data type.
A simple way to this task may be to drop and re-create the identity column with same name and properties. When re-created it would be optimized and have no gaps among values. Performing drop and re-create through T-SQL would be much more efficient than through SSMS (have a look at my article on ALTER TABLE operations through T-SQL and SSMS). But through T-SQL you would not be able to re-create the column at any position in table but last. So if you have used select * any where in application code (that i always try to avoid), then change of column order may raise issues. In that case you may use SSMS to delete and re-create the identity column at required position in table. Consider the following example for above process

 -- Create table for test  
 if exists (select * from sys.objects where name = 'IDTest' )  
 drop table IDTest  
 GO  
 create table  
 IDTest(ID smallint primary key identity (1,1), name varchar(50))  
 GO  
 -- Populate the table  
 insert into IDTest values ('Atif ')  
 GO 300  
 -- Delete records  
 delete from IDTest where id between 11 and 60  
 GO  
 -- Verify the gap created by delete operation  
 select * from IDTest order by ID  
 GO  

removing gaps in values of identity column

We may look that gap exists in values of identity column after delete operation. Now we may apply the drop and re-create approach for identity column to remove the gaps and make the column optimized. For task through SSMS open table in designer, delete the identity column and then create it again with same properties by using insert column option.
I have used T-SQL to drop and recreate the identity column.

 
-- Drop the primary key constraint first (do not forget to change the constraint name).  
 ALTER TABLE IDTest DROP PK__IDTest__3214EC2720C1E124  
 GO  
 -- drop the identity column  
 ALTER TABLE IDTest DROP COLUMN ID  
 GO  
 -- Re-create the identity column  
 ALTER TABLE IDTest ADD ID SMALLINT PRIMARY KEY IDENTITY (1,1)  
 GO  

At this point identity column ID is created and populated without any gap. But it would be created as last column with respect to columns order in table.

Duplication error messages related to seed and incremental value

Going back to second problem, it relates to SEED property of identity column. If after manipulating seed value, errors are being generated for duplication then follow these steps.

  1. Get maximum value in identity column
  2. ReSeed by providing the maximum value

It should be kept in mind that value retrieved through max() function may not necessarily be the maximum identity value. That may be the case when some rows have been deleted with last (highest) identity values. However setting the max value as SEED would work fine and no duplication issue would be raised.

 
-- Get last identity value  
 SELECT Max(ID) from IDTest  
 GO  

As we have 250 as max value in our re-created identity column so use it for RESEED

 -- Get last identity value  
 DBCC CHECKIDENT ('IDTest', RESEED, 250)  
 GO  

In this way duplication due to invalid SEED value may be prevented. It is considerable that DROP and RE-CREATE of identity column may also solve duplication error issue.

  • Feb 03 / 2012
  • 2
DBA Interview questions, dbDigger, Joins, T-SQL Interview Questions, T-SQL Tips and Tricks

Complete the T-SQL query to get the required result

I came across an interesting question. To get those values of table1 that are not there in table2. This task can be completed very easily by using the EXCEPT operation. However i was required to get the result by appending just a single condition in provided T-SQL code. The trick was about using joins concept to get the required result. Create and populate two tables through following code.

 
-- Create first table 
create table Table1 (id smallint, Item varchar(25))  
GO  
 
-- Create second table  
create table Table2 (id smallint, Item varchar(25)) 
GO  

-- Populate table1  
insert into table1 select 1,'Item1'  
union all  select 2, 'Item2' 
union all  select 3, 'Item3'  
union all  select 4, 'Item4'  
GO  

-- Populate table2  
insert into table2 values (3,'Item3')  
GO  

The required result is as shown in the snap

Solve T-SQL puzzle

And just add a single condition either in join clause or in where clause of following query to get the result

 -- Complete the script to get required result  
 select t1.*  
 from table1 t1 left outer join table2 t2  
 on t1.id = t2.id  
 and t1.item = t2.item  
 -- Add a condition here to get the required result  

And below is the single line condition that is required to get the required result

 
where t2.id is null  

Complete query would be as following

 -- Complete script to get required result  
 select t1.*  
 from table1 t1 left outer join table2 t2  
 on t1.id = t2.id  
 and t1.item = t2.item  
 where t2.id is null  
Consult us to explore the Databases. Contact us