:::: MENU ::::

Posts Categorized / T-SQL Tips and Tricks

  • 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 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
GO

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
  • EXECUTE AS User

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]
GO

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.

Consult us to explore the Databases. Contact us