:::: MENU ::::

Posts Categorized / T-SQL Tips and Tricks

  • 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  
 (ID SMALLINT IDENTITY(1,1),  
 CNIC CHAR(13), Lang VARCHAR(50),  
 OralSkill TINYINT, WrittenSkill TINYINT)  
 GO  
 -- Populate table with sample data  
 INSERT INTO EmpLanguages  
 SELECT '1111111111111','Urdu', 4, 4  
 UNION ALL  
 SELECT '1111111111111','Arabic', 3, 2  
 UNION ALL  
 SELECT '1111111111111','English', 3, 3  
 UNION ALL  
 SELECT '1111111111111','Pashto', 3, 2  
 GO  
 -- verify the data  
 SELECT * FROM EmpLanguages  
 GO  

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  
 GO  

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'  
 GO  
 -- Again verify the data  
 SELECT * FROM EmpLanguages  
 GO  

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

 
CREATE TRIGGER [RestrictSSMSLogIn]  
 ON ALL SERVER WITH EXECUTE AS 'AppUser'  
 FOR LOGON  
 AS  
 BEGIN  
 DECLARE @ip VARCHAR(16)  
 SELECT @ip = client_net_address  
 FROM sys.dm_exec_connections WHERE session_id = @@SPID  
 IF ORIGINAL_LOGIN()= 'AppUser' AND  
 ((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 <> '192.168.1.202') -- Provide IP address of web server here  
 ROLLBACK;  
 END  
 GO  
 ENABLE TRIGGER [RestrictSSMSLogIn] ON ALL SERVER  
 GO  

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