Monthly Archives: July 2009
Execute Most Frequent Queries with Keyboard Shortcut Keys
(If you are looking for commonly used keyboard shortcut keys for Sql Server Management Studio click here)
With my recent job, I am facing a problem to type frequently used queries again and again in query analyzer to execute. Searched for the best solution to avoid my typing practice and I found following ways to accomplish my task
- Write down queries that I need frequently and save them separately on my hard drive and to get them back, click on open file and connect it to my required Database Server and EXECUTE.
- Using Project
- Create a new Project by selecting a template SQL SERVER SCRIPTS
- Go to Solution Explorer, right click Queries folder and click on New Query.
- Type your new query and save it.
- Now we can use query by just double click on your required one.
Second method really helped me to write down my long query scripts and run these scripts with out retyping them, and without any trouble to browse and find these queries.
But for short queries and some long scripts too, which are more often used in my current projects. I tried to find out more quick ways and come a cross to Keyboard Shortcuts Keys to execute these most frequently used queries.
Is it possible in Sql Server Management Studio?
Yes, though with limitations, but it is possible to execute your frequently used queries with shortcuts keys.
Limits:
1. Execute queries by writing them in single line.
2. Max 32767 characters long query is possible
3. Parameters can’t be declared
4. Store Procedures, that doesn’t require any parameter value or with default values are possible to execute
How to do?
1. Open Sql Server Management Studio
2. In menu bar click on tools and select options
3. Expend “Environment” and click on Keyboard
We can find that shortcut keys for three commonly used System Stored Procedures are already created.
• sp_help
• sp_who
• sp_lock
Lets create shourtcut key for our own query
(for adventureworks )
SELECT * FROM Production.Product
Generate Foreign Key for All Databases
sp_MSforeachdb
‘USE ?
IF DB_ID(”?”) > 4 — Skip system databases
BEGIN
EXEC (”
SELECT ””ALTER TABLE ””+OBJECT_NAME(f.parent_object_id)+
”” ADD CONSTRAINT”” + f.name + ”” FOREIGN KEY””+””(””+COL_NAME(fc.parent_object_id,fc.parent_column_id)+””)””
+””REFRENCES ””+OBJECT_NAME (f.referenced_object_id)+””(””+COL_NAME(fc.referenced_object_id,
fc.referenced_column_id)+””)”” as Scripts
FROM .sys.foreign_keys AS f
INNER JOIN .sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
”)
END’
Script to Generate All Primary and Foreign Keys
–***********Generate create script for all Primary Keys
DECLARE cPK CURSOR FOR
SELECT DISTINCT C.TABLE_NAME, C.CONSTRAINT_NAME , F.NAME
FROM SYS.INDEXES I
INNER JOIN SYS.FILEGROUPS F
ON I.DATA_SPACE_ID = F.DATA_SPACE_ID
INNER JOIN SYS.ALL_OBJECTS O
ON I.[OBJECT_ID] = O.[OBJECT_ID]
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
ON O.NAME = C.TABLE_NAME
WHERE C.CONSTRAINT_TYPE = ‘PRIMARY KEY’
ORDER BY C.TABLE_NAME
DECLARE @PkTable SYSNAME
DECLARE @PkName SYSNAME
DECLARE @FileName SYSNAME
— Loop through all the primary keys
OPEN cPK
FETCH NEXT FROM cPK INTO @PkTable, @PkName , @FileName
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @PKSQL NVARCHAR(4000) SET @PKSQL = ”
SET @PKSQL = ‘ALTER TABLE ‘ + @PkTable + ‘ ADD CONSTRAINT ‘ + @PkName +
‘ PRIMARY KEY CLUSTERED (‘
— Get all columns for the current primary key
DECLARE cPKColumn CURSOR FOR
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = @PkTable AND CONSTRAINT_NAME = @PkName
ORDER BY ORDINAL_POSITION
OPEN cPKColumn
DECLARE @PkColumn SYSNAME
DECLARE @PkFirstColumn BIT SET @PkFirstColumn = 1
— Loop through all columns and append the sql statement
FETCH NEXT FROM cPKColumn INTO @PkColumn
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@PkFirstColumn = 1)
SET @PkFirstColumn = 0
ELSE
SET @PKSQL = @PKSQL + ‘, ‘
SET @PKSQL = @PKSQL + @PkColumn
FETCH NEXT FROM cPKColumn INTO @PkColumn
END
CLOSE cPKColumn
DEALLOCATE cPKColumn
SET @PKSQL = @PKSQL + ‘)’ + ‘ ON ‘+@FileName
— Print the primary key statement
PRINT @PKSQL
FETCH NEXT FROM cPK INTO @PkTable, @PkName, @FileName
END
CLOSE cPK
DEALLOCATE cPK
—*********Generate create script for all Foreign Keys
SELECT ‘ALTER TABLE ‘+OBJECT_NAME(F.PARENT_OBJECT_ID)+ ‘ ADD CONSTRAINT’
+ F.NAME + ‘ FOREIGN KEY’+‘(‘+COL_NAME(FC.PARENT_OBJECT_ID,FC.PARENT_COLUMN_ID)+
‘)’+‘REFRENCES ‘+OBJECT_NAME (F.REFERENCED_OBJECT_ID)+‘(‘
+COL_NAME(FC.REFERENCED_OBJECT_ID,FC.REFERENCED_COLUMN_ID)+‘)’
FROM SYS.FOREIGN_KEYS AS F
INNER JOIN SYS.FOREIGN_KEY_COLUMNS AS FC
ON F.OBJECT_ID = FC.CONSTRAINT_OBJECT_ID
Who acquired the exclusive locks
Here is a simple query to find out the culprit…
SELECT session_id,host_name,request_mode,last_request_start_time,
FROM sys.dm_tran_locks INNER JOIN sys.databases ON sys.dm_tran_locks.resource_database_id =sys.databases.database_id
INNER JOIN sys.dm_exec_sessions ON sys.dm_exec_sessions.session_id=sys.dm_tran_locks.request_session_id
WHERE resource_type ‘DATABASE’
AND request_mode LIKE ‘%X%’
AND name =’YourDatabaseName’
Now you have the session_id and name of person who is responsible for Exclusive Locks, IF YOU WANT, you can kill his session by following simple query
KILL session_number
Example: KILL 253
Uniqueidentifier Column as Primary Key, a worst choice
GUID or int as Primary key ???
Though it is not necessary that your Primary Key column is always a cluster index too. By default, Sql Server creates cluster index on column or group of columns which you have declared as your table PK, and most of DBAs don’t like to go against this default behavior of Sql Server.
But problem arises when for uniqueness a column with uniqueidentifier data type is added, for surrogate key to make it Primary Key, finally for your table.
The GUID is a wide column (16 bytes to be specific) and contains a unique combination of 33 uppercase and numeric characters. This column because it is the primary key is going to be stored in, of course, the clustered index .
Also, if a GUID is used instead of an integer identity column then the 33 characters need to be matched for each row that is returned using that column in the where clause.
If a high volume of inserts are done on these tables then GUID’s being large will contribute to page splits, as will the fact that NEWID() generates a random value, which could place a new record on any of the data pages will cause performance problems.
Recommendations
- INT must be used as Primary Key instead of GUID because:
- INT takes only 4 bytes, saving your physical and memoray storage.
- INT as primary key (identity) creates incremental values resulting less then 1% of indexes fregmention during heavy insert.
- There are T-SQL operators available for INT like >,= and <
How to install Sql Server Management Studio
Click on CLIENT COMPONENTS, install it completly or you can just select Management Tools. What ever you select DON’T forget to select “Entire feature will be installed on local hard drive”
http://rcm.amazon.com/e/cm?t=co04b8-20&o=1&p=8&l=bpl&asins=B004CYEQF0&fc1=000000&IS2=1<1=_blank&m=amazon&lc1=0000FF&bc1=FFFFFF&bg1=FFFFFF&f=ifrhttp://rcm.amazon.com/e/cm?t=co04b8-20&o=1&p=8&l=bpl&asins=B002FQJT3Q&fc1=000000&IS2=1<1=_blank&m=amazon&lc1=0000FF&bc1=FFFFFF&bg1=FFFFFF&f=ifrhttp://rcm.amazon.com/e/cm?t=co04b8-20&o=1&p=8&l=bpl&asins=0735626049&fc1=000000&IS2=1<1=_blank&m=amazon&lc1=0000FF&bc1=FFFFFF&bg1=FFFFFF&f=ifr
Create DDL changes log
Its a common question, that How to trace changes made in database objects. Like,
Who has drooped my table ?
Who made changes in my view?
Who made changes in store procedure/Function?
You can solve this problem by creating a DDL (Data Definition Language) i.e. Create, Update, Drop trigger, to trace changes made in a database.
–==============CREATE table to store changes
CREATE TABLE [dbo].[ChangeLog](
[LogId] [int] IDENTITY(1,1) NOT NULL,
[DatabaseName] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EventType] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ObjectName] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ObjectType] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SqlCommand] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EventDate] [datetime] NOT NULL CONSTRAINT [DF_EventsLog_EventDate] DEFAULT (getdate()),
[LoginName] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
go
–============= CREATE DATABASE TRIGGER TO INSERT CHANGES INTO dbo.changelog TABLE ===========
CREATE trigger backup_objects
on database
for create_procedure, alter_procedure, drop_procedure,
create_table, alter_table, drop_table,
create_function, alter_function, drop_function
as
set nocount on
declare @data xml
DECLARE @client_ip VARCHAR(15)
set @data = EVENTDATA()
SELECT @client_ip = client_net_address
FROM sys.dm_exec_connections
WHERE session_id =@data.value(‘(/EVENT_INSTANCE/SPID)[1]’, ‘varchar(256)’)
insert into YOURDATABASE.dbo.changelog(databasename, eventtype,
objectname, objecttype, sqlcommand, loginname)
values(
@data.value(‘(/EVENT_INSTANCE/DatabaseName)[1]’, ‘varchar(256)’),
@data.value(‘(/EVENT_INSTANCE/EventType)[1]’, ‘varchar(50)’),
@data.value(‘(/EVENT_INSTANCE/SchemaName)[1]’, ‘varchar(256)’) +’.’+
@data.value(‘(/EVENT_INSTANCE/ObjectName)[1]’, ‘varchar(256)’),
@data.value(‘(/EVENT_INSTANCE/ObjectType)[1]’, ‘varchar(25)’),
@data.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]’, ‘varchar(max)’),
@data.value(‘(/EVENT_INSTANCE/LoginName)[1]’, ‘varchar(256)’)+'(‘+@client_ip+’)’
)
Connect SQL 