:::: MENU ::::

Posts Categorized / T-SQL Tips and Tricks

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

  • Feb 28 / 2012
  • 0
dbDigger, Monitoring and Analysis, Reference Articles Archival, SQL Server logs, System Stored Procedures, T-SQL Tips and Tricks

Reading SQL Server logs by using T-SQL

SQL Server logs are valuable way to analyze condition and activities on SQL Server. Activities may be related to logins, sessions, backups, permission errors etc. SSMS GUI provides way to read SQL Server logs however also there are powerful T-SQL alternates that provide facilities filters for dates, keywords and specific log files. Click here to read a very well written article about reading SQL Server logs through T-SQL. Also do not forget to read valuable comments under this article.

  • Feb 23 / 2012
  • 0
dbDigger, T-SQL Scripts, T-SQL Tips and Tricks

Select and Insert rows in same table with some different values

A requirement may arise when we have to select specific rows and insert these in same table with some values different. There may be several scenarios for it. Following example would cover the requirement and process involved to complete the task.
We have a table with information about language skills of employees. We are required to select records of an employee and insert these for different employee. All other field values are same except the employee number that we would provide explicitly in script. Consider following script

-- Craete table for demo  
 CREATE TABLE EmpLanguages  
 CNIC CHAR(13), Lang VARCHAR(50),  
 OralSkill TINYINT, WrittenSkill TINYINT)  
 -- Populate table with sample data  
 INSERT INTO EmpLanguages  
 SELECT '1111111111111','Urdu', 4, 4  
 SELECT '1111111111111','Arabic', 3, 2  
 SELECT '1111111111111','English', 3, 3  
 SELECT '1111111111111','Pashto', 3, 2  
 -- verify the data  
 SELECT * FROM EmpLanguages  

We have values for employee with CNIC 1111111111111, that we have to insert for employee with CNIC 2222222222222. First of all get columns list instead of manually writing these in query

 -- Get columns  
 SP_HELP EmpLanguages  

Now copy the columns and construct following script.

 -- Insert same records for different employees  
 INSERT INTO EmpLanguages  
 SELECT '2222222222222' ,Lang, OralSkill, WrittenSkill  
 FROM EmpLanguages  
 WHERE CNIC = '1111111111111'  
 -- Again verify the data  
 SELECT * FROM EmpLanguages  

Verification of data shows that task is complete. This method may be applied for various other scenarios with little modification.

  • Feb 20 / 2012
  • 0
dbDigger, SSMS tips and tricks, T-SQL Tips and Tricks

Copy and paste among multiple lines in SSMS

SQL Server Management Studio (SSMS) is my favorite tool and m fan of it right from its start with SQL Server 2005. With every new release SSMS got more coll features for DBAs. Recently i came across a very useful feature in SSMS query pane. It allows to select specific content from multiple lines in query pane. This feature is extremely helpful when we are required to select table names, column names or even values from the script. The selected values may be deleted or copied as required. And may be paste in same alignment any where among the text.

Basically the technique is to hold the ALT key and drag the mouse on required text in SSMS query pane. Following is an example of its usage and you may use it for many tasks in more creative manner.
Suppose i am required to select just table names given in three seperate T-SQL statements. I would press ALT key and start dragging the mouse on required data. The selection would be as shown in following snap
select data from multiple lines in SSMS

Now we may copy data for onward use. It is notable that during the paste process data would preserve its alignment that was during the copy process. Spend some time to play with this cool feature and make use of it.

  • Feb 16 / 2012
  • 0
dbDigger, Logins and Users, Logon Triggers, Security and Permissions, T-SQL Scripts, T-SQL Tips and Tricks, Triggers

Restrict logon for a SQL Server login through all but one IP

In a previous article i discussed that how to restrict SQL Server login to connect from out side the application. Logon trigger was utilize to achieve this task. The puprose behind it was to make sure that permission rich logins used in application should not be allowed to access SQL Server by any way other than managed application code.
However it is possible that any one may use that login through application for unit testing or to execute ad-hoc code from inside the application code. I was asked to also restrict such access that is adhoc or for unit testing from developers desktops. So the requirement is that login used in application could only access SQL Server after fulfilling two conditions

  1. Login is requesting to connect from inside the application code
  2. Login is requesting from no where else but web server (application server)

My previous article covers the first requirement but it lacks to check the second requirement. Following code (modified version of previous article) would make sure to check both the conditions prior to granting login access

 SELECT @ip = client_net_address  
 FROM sys.dm_exec_connections WHERE session_id = @@SPID  
 ((SELECT TOP 1 Program_Name  
 FROM sys.dm_exec_sessions  
 WHERE is_user_process = 1  
 AND original_login_name = 'AppUser'  
 Order By Session_Id Desc)  
 <>'.Net SqlClient Data Provider'  
 or @ip <> '') -- Provide IP address of web server here  

In this way the login used for application is restricted to access only from web server through application code only.

  • 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  
 create table  
 IDTest(ID smallint primary key identity (1,1), name varchar(50))  
 -- Populate the table  
 insert into IDTest values ('Atif ')  
 GO 300  
 -- Delete records  
 delete from IDTest where id between 11 and 60  
 -- Verify the gap created by delete operation  
 select * from IDTest order by ID  

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  
 -- drop the identity column  
 -- Re-create the identity column  

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  

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

 -- Get last identity value  

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.

Consult us to explore the Databases. Contact us