:::: MENU ::::

Posts Categorized / T-SQL Tips and Tricks

  • 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))  
 -- Insert 100 values in identity column  
 GO 100  
 -- Verify the values   
 SELECT * FROM DemoTable  
 -- 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 14 / 2013
  • 1
dbDigger, T-SQL Tips and Tricks

Count characters in MS Word 2007 document

Working with my article for MSSQLTips.com i was required to put roughly 4000 characters in code of a stored procedure. After pasting the text in a word document i looked for any information about characters. However word 2007 was displaying number of words at bottom bar.

Count characters in MS Word 2007 document

So in this situation the easy and handy method to get count for characters, words, paragraphs, pages and lines just click on the words count being displayed at bottom bar of document. It would provide all required information.

get count for characters, words, paragraphs, pages and lines in word document

I beleive there would be other methods to access this information but i think this one is most handy and applicable to other versions of MS Word.

  • Oct 18 / 2012
  • 0
dbDigger, MS Excel, T-SQL Tips and Tricks

Build queries by concatenating cells in excel sheet

Often it is required to update the provided values based on specific data in excel sheet. For example we have two column data in excel sheet. Columns are ID and EmpName. EmpNames from excel sheet should be the updated values for ID  provided in excel sheet.
Required query for updating the name to ‘Shehzad’ would be as following for ID = 2

Update EMPS set EmpName = 'Shehzad' where ID = 2

It would be time consuming to manually build the query or first import the data in a table for required update operation. The easy way would be to create the query by concatenating the cells and strings inside the excel sheet as shown below.

Build queries by concatenating cells in excel sheet


I have used following formula to build the required query for my scenario
= “Update emps set name = ‘” & B2 &”‘ where ID = “& A2
Simply enclose the string in quotation marks and join the cells and strings through & sign. After implementing the formula just drag the cell down to all rows down.

There is another function provided for same purpose. Using concatenate function would also complete the task as shown below.

Build queries by concatenating cells in excel sheet through concatenate function

In this case i have used the concatenate function in the following way
=CONCATENATE(“Update emps set EmpName = ‘”,B2,”‘ where ID = “,A2) 
It would produce the same results as by first method.
By using these simple techs a lot of time and effort may be saved.

  • Jun 26 / 2012
  • 0
dbDigger, Logins and Users, Monitoring and Analysis, Security and Permissions, T-SQL Tips and Tricks

Impersonating a login or user in SQL Server

SQL Server deals with Logins at server level and in the databases with users. Databases operate with various permissions on different objects. Permissions are assigned explicitly or implied. It is one of the primary responsibilities of a DBA to monitor and manage the permissions so that data and information security is ensured.
As part of managing the security and permissions architecture, DBA may be required to check a specific context or permission  for any account. You do not need to get password from the account owner to get login and then check the context.A simple and effective method is available. Just use the EXECUTE AS statement and you are in required context.
Actually EXECUTE AS is meant to manage more than just this simple task. We may use it for stored procedures context as well. EXECUTE AS may be used in two flavors

  • EXECUTE AS Login

As the above mentioned flavors indicate, while working with server level permissions EXECUTE AS Login may be used to impersonate the server level permissions of a login. While within a database context, EXECUTE AS User may be used to switch the context to a specific user in a DB. Before going on usage of EXECUTE AS, let me clarify that not every one may impersonate by using the EXECUTE AS. sysadmin and dbo has impersonation rights already with them at server and db level respectively. However impersonation permissions may be granted to required login/user. Discussing the internals and flow of impersonation is beyond the scope of this post.
Following is the code and result to prove the impersonation of a login by using EXECUTE AS Login

Impersonate SQL Server Login by Using EXECUTE AS LOGIN

We may analyze that login context was switched and was revert at the end. According to BOL impersonation context is changed back in following three conditions.

  • Another EXECUTE AS statement is run
  • A REVERT statement is run
  • The session is dropped

Following code and result would demonstrate the impersonation of a user through EXECUTE AS User statement

Impersonate SQL Server DB User Using EXECUTE AS USER

Impersonation through EXECUTE AS statement was intrioduced in SQL Server 2005. Before this SETUSER statement was used. SETUSER may still be used in new versions but as a deprecated feature.
Also read a EXECUTE AS related tip on mssqltips.com.

  • Apr 25 / 2012
  • 0
dbDigger, Linked Servers and remote data, T-SQL Tips and Tricks

Accessing linked server data in SELECT or JOIN clause

Configured Linked servers allow to access heterogeneous and multiple source data. We may directly select, join the linked data with our local server data. Linked server may be configured for any OLEDB data source.
Once linked server is configured and working, here is a simple syntax to access its data in select or join  statement of T-SQL. For following linked server

Linked server name            DBASS2008
Database Name                    LinkedDB
Schema                                  dbo
Table                                      Linked table

To access the data in above provided linked server we would have to use fully qualified four part name pointing to linked server. It would be like [ServerName].[DBName].[SchemaName].[ObjectName]

select * from [DBASS2008].[LinkedDB].[dbo].[Linked table]

Same qualified syntax may be used while accessing data in join. If valid qualified syntax is not used then error message would be generated related to invalid object.

  • Mar 30 / 2012
  • 0
DBA best practices, dbDigger, Performance Tunning and Optimization, SSMS tips and tricks, T-SQL Tips and Tricks

Move the columns to required position in SSMS result pane

It is always good to have a clear idea of filtered data and order of columns for any required result. However, consider the scenario that you have executed a resource intensive query and got the data in SSMS result pane. And after having a look at the result set you perceive that moving a column prior to a specific one may improve the report.
A option may be to change the order of columns in query and re-generating the result set to required order. But this would again consume resources for that resource intensive query. Best solution for changing column order in result set would be by dragging the column to required position.
Here is an example, i have retrieved result from AdventureWorks database and later moved the column [Phone] from last to second last position.

Swaping the columns in SSMS results pane

This simple tip may prevent the re-execution of query for column re-ordering purpose.

Consult us to explore the Databases. Contact us