Tuesday, July 28, 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


  1. 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.
  2. 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

Press OK button to save your changes and restart Sql Server Management Studio. Now open a new query (Ctrl+N) for your adventureworks database and press Ctrl+3
Here is your result without typing any query in qurery analyzer
You can also execute stored procure by assigning it a shortcut key by above mentioned method. For demonstration we will create a commonly used stored procedure written by Tara Kizar, isp_ALTER_INDEX . Before assigning it a shortkut key, edit this stored procedure and assign a default value for @dbName.
To execute stored procedure just press Ctlr + 4

Thursday, July 23, 2009

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

Wednesday, July 22, 2009

Who acquired the exclusive locks

In an environment where more then one developers are debugging their code, and unfortunately database is same, they usually place different kinds of locks and most of the times these are Exclusive locks which force other developers to wait for the first one to complete his work.

But problem is, Who is acquiring the locks?
Here is a simple query to find out the culprit...

SELECT session_id,host_name,request_mode,last_request_start_time,
resource_type, resource_description
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

  1. INT must be used as Primary Key instead of GUID because:
  2. INT takes only 4 bytes, saving your physical and memoray storage.
  3. INT as primary key (identity) creates incremental values resulting less then 1% of indexes fregmention during heavy insert.
  4. There are T-SQL operators available for INT like >,= and <

Monday, July 20, 2009

How to install Sql Server Management Studio


How to install Sql Server Management Studio???Why I can’t install Sql Server Management Studio???
Unable to install Sql Server Management Studio???

SQL Server Management Studio is a tool included with Microsoft SQL Server 2005 and later versions for configuring, managing, and administering all components within Microsoft SQL Server. The tool includes both script editors and graphical tools which work with objects and features of the server.

Some time it becomes a mystery to install Sql Server Management Studio. Normally you click setup file to install Sql Server 2005 and its components, though you have selected Client Components (Which includes Sql Server Management Studio) to install with other components, but after installation is complete, you can’t find Sql Server Management Studio in installed programs list.
No need to uninstall and re-install your whole Sql Server 2005. Leave the installed components as is and find out SqlRun_Tools setup file to execute, from following path.

 Click Change Installed Components ...



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"


Click Next button and let it install and here is Sql Server Management Studio…….
Enjoy.... ;)



Installing Only SQL Server Management Studio

If you are interested to install SQL Server Management Studio for Express Edition then following video at youtube will be helpful for you

How to install SQL Server 2008 Management Studio Express



(And If you are looking for only client tools (Sql Server Management Studio 2008) installation in Sql Server 2008. You must visit http://www.mssqltips.com/tip.asp?tip=1807 )

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+')'
)