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.
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.
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.
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
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.
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
- Login is requesting to connect from inside the application code
- 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.
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
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.
- Get maximum value in identity column
- 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.
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
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