If possible, avoid using SQL Server cursors. They generally use a lot of SQL Server resources and reduce the performance and scalability of your applications. If you need to perform row-by-row operations, try to find another method to perform the task.
Here are some alternatives to using a cursor:
• Use WHILE LOOPS
• Use temp tables
• Use derived tables
• Use correlated sub-queries
• Use the CASE statement
• Perform multiple queries
If you do find you must use a cursor, try to reduce the number of records to process.
One way to do this is to move the records that need to be processed into a temp table first, and then create the cursor to use the records in the temp table, not from the original table. This of course assumes that the subsets of records to be inserted into the temp table are substantially less than those in the original table.
The lower the number of records to process, the faster the cursor will finish.
If you have no choice but to use a server-side cursor in your application, try to use a FORWARD-ONLY or FAST-FORWARD, READ-ONLY cursor. When working with unidirectional, read-only data, use the FAST_FORWARD option instead of the FORWARD_ONLY option, as it has some internal performance optimizations to speed performance. This type of cursor produces the least amount of overhead on SQL Server.
If you are unable to use a fast-forward cursor, then try the following cursors in this order, until you find one that meets your needs. They are listed in the order of their performance characteristics, from fastest to slowest: dynamic, static, and keyset.
Avoid using static/insensitive and keyset cursors, unless you have no other choice. This is because they cause a temporary table to be created in TEMPDB, which increases overhead and can cause resource contention issues.
If you have no choice but to use cursors in your application, try to locate the SQL Server tempdb database on its own physical device for best performance. This is because cursors use the tempdb for temporary storage of cursor data. The faster your disk array running tempdb, the faster your cursor will be.
Using cursors can reduce concurrency and lead to unnecessary locking and blocking. To help avoid this, use the READ_ONLY cursor option if applicable, or if you need to perform updates, try to use the OPTIMISTIC cursor option to reduce locking. Try to avoid the SCROLL_LOCKS cursor option, which reduces concurrency.
When you are done using a cursor, don't just CLOSE it, you must also DEALLOCATE it. Deallocation is required to free up the SQL Server resources used by the cursor. If you only CLOSE the cursor, locks are freed, but SQL Server resources are not. If you don't DEALLOCATE your cursors, the resources used by the cursor will stay allocated, degrading the performance of your server until they are released.
If it is appropriate for your application, try to load the cursor as soon as possible by moving to the last row of the result set. This releases the share locks created when the cursor was built, freeing up SQL Server resources.
If you have to use a cursor because your application needs to manually scroll through records and update them, try to avoid client-side cursors, unless the number of rows is small or the data is static. If the number of rows is large, or the data is not static, consider using a server-side keyset cursor instead of a client-side cursor. Performance is usually boosted because of a reduction in network traffic between the client and the server. For optimum performance, you may have to try both types of cursors under realistic loads to determine which is best for your particular environment.
When using a server-side cursor, always try to fetch as small a result set as possible. This includes fetching only those rows and columns the client needs immediately. The smaller the cursor, no matter what type of server-side cursor it is, the fewer resources it will use, and performance will benefit.
If you need to perform a JOIN as part of your cursor, keyset and static cursors are generally faster than dynamic cursors, and should be used when possible.
Showing posts with label SQL SERVER. Show all posts
Showing posts with label SQL SERVER. Show all posts
Wednesday, March 23, 2011
Tuesday, March 1, 2011
List of Querying the SQL Server System Catalog
How do I find the data types of the columns of a specified table?
Before you run the following query, replace and with valid names.
USE;
GO
SELECT c.name AS column_name
,c.column_id
,SCHEMA_NAME(t.schema_id) AS type_schema
,t.name AS type_name
,t.is_user_defined
,t.is_assembly_type
,c.max_length
,c.precision
,c.scale
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
WHERE c.object_id = OBJECT_ID('')
ORDER BY c.column_id;
GO
How do I find all the owners of entities contained in a specified schema?
USE;
GO
SELECT 'OBJECT' AS entity_type
,USER_NAME(OBJECTPROPERTY(object_id, 'OwnerId')) AS owner_name
,name
FROM sys.objects WHERE SCHEMA_NAME(schema_id) = ''
UNION
SELECT 'TYPE' AS entity_type
,USER_NAME(TYPEPROPERTY(SCHEMA_NAME(schema_id) + '.' + name, 'OwnerId')) AS owner_name
,name
FROM sys.types WHERE SCHEMA_NAME(schema_id) = ''
UNION
SELECT 'XML SCHEMA COLLECTION' AS entity_type
,COALESCE(USER_NAME(xsc.principal_id),USER_NAME(s.principal_id)) AS owner_name
,xsc.name
FROM sys.xml_schema_collections AS xsc JOIN sys.schemas AS s
ON s.schema_id = xsc.schema_id
WHERE s.name = '';
GO
How do I find all the tables that do not have a primary key?
USE;
GO
SELECT SCHEMA_NAME(t.schema_id) AS schema_name
,t.name AS table_name
FROM sys.tables t
WHERE object_id NOT IN
(
SELECT parent_object_id
FROM sys.key_constraints
WHERE type_desc = 'PRIMARY_KEY_CONSTRAINT' -- or type = 'PK'
);
GO
Or, you can run the following query.
USE;
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name
,name AS table_name
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 0
ORDER BY schema_name, table_name;
GO
How do I find all the tables that do not have an index?
USE;
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name
,name AS table_name
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 0
ORDER BY schema_name, table_name;
GO
How do I find all the tables that have an identity column?
USE;
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name
, t.name AS table_name
, c.name AS column_name
FROM sys.tables AS t
JOIN sys.identity_columns c ON t.object_id = c.object_id
ORDER BY schema_name, table_name;
GO
Or, you can run the following query.
USE;
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name
,name AS table_name
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'TableHasIdentity') = 1
ORDER BY schema_name, table_name;
GO
How do I find all the stored procedures in a database?
USE;
GO
SELECT name AS procedure_name
,SCHEMA_NAME(schema_id) AS schema_name
,type_desc
,create_date
,modify_date
FROM sys.procedures;
GO
How do I find the parameters for a specified stored procedure or function?
USE;
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name
,o.name AS object_name
,o.type_desc
,p.parameter_id
,p.name AS parameter_name
,TYPE_NAME(p.user_type_id) AS parameter_type
,p.max_length
,p.precision
,p.scale
,p.is_output
FROM sys.objects AS o
INNER JOIN sys.parameters AS p ON o.object_id = p.object_id
WHERE o.object_id = OBJECT_ID('')
ORDER BY schema_name, object_name, p.parameter_id;
GO
How do I find all the user-defined functions in a database?
USE;
GO
SELECT name AS function_name
,SCHEMA_NAME(schema_id) AS schema_name
,type_desc
,create_date
,modify_date
FROM sys.objects
WHERE type_desc LIKE '%FUNCTION%';
GO
How do I find all views in a database?
USE;
GO
SELECT name AS view_name
,SCHEMA_NAME(schema_id) AS schema_name
,OBJECTPROPERTYEX(object_id,'IsIndexed') AS IsIndexed
,OBJECTPROPERTYEX(object_id,'IsIndexable') AS IsIndexable
,create_date
,modify_date
FROM sys.views;
GO
How do I find all the entities that have been modified in the last N days?
USE;
GO
SELECT name AS object_name
,SCHEMA_NAME(schema_id) AS schema_name
,type_desc
,create_date
,modify_date
FROM sys.objects
WHERE modify_date > GETDATE() -
ORDER BY modify_date;
GO
How do I view the definition of a server-level trigger?
SELECT definition
FROM sys.server_sql_modules;
GO
How do I find the columns of a primary key for a specified table?
USE;
GO
SELECT i.name AS index_name
,ic.index_column_id
,key_ordinal
,c.name AS column_name
,TYPE_NAME(c.user_type_id)AS column_type
,is_identity
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns AS c
ON ic.object_id = c.object_id AND c.column_id = ic.column_id
WHERE i.is_primary_key = 1
AND i.object_id = OBJECT_ID('');
GO
Or, you can use the COL_NAME function as shown in the following example.
USE;
GO
SELECT i.name AS index_name
,COL_NAME(ic.object_id,ic.column_id) AS column_name
,ic.index_column_id
,key_ordinal
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.object_id = ic.object_id AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1
AND i.object_id = OBJECT_ID('');
GO
How do I find the columns of a foreign key for a specified table?
USE;
GO
SELECT
f.name AS foreign_key_name
,OBJECT_NAME(f.parent_object_id) AS table_name
,COL_NAME(fc.parent_object_id, fc.parent_column_id) AS constraint_column_name
,OBJECT_NAME (f.referenced_object_id) AS referenced_object
,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS referenced_column_name
,is_disabled
,delete_referential_action_desc
,update_referential_action_desc
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.object_id = fc.constraint_object_id
WHERE f.parent_object_id = OBJECT_ID('');
How do I determine if a column is used in a computed column expression?
USE;
GO
SELECT OBJECT_NAME(object_id) AS object_name
,COL_NAME(object_id, column_id) AS computed_column
,class_desc
,is_selected
,is_updated
,is_select_all
FROM sys.sql_dependencies
WHERE referenced_major_id = OBJECT_ID('')
AND referenced_minor_id = COLUMNPROPERTY(referenced_major_id, '', 'ColumnId')
AND class = 1;
GO
How do I find all the columns that are used in a computed column expression?
USE;
GO
SELECT OBJECT_NAME(d.referenced_major_id) AS object_name
,COL_NAME(d.referenced_major_id, d.referenced_minor_id) AS column_name
,OBJECT_NAME(referenced_major_id) AS dependent_object_name
,COL_NAME(d.object_id, d.column_id) AS dependent_computed_column
,cc.definition AS computed_column_definition
FROM sys.sql_dependencies AS d
JOIN sys.computed_columns AS cc
ON cc.object_id = d.object_id AND cc.column_id = d.column_id AND d.object_id=d.referenced_major_id
WHERE d.class = 1
ORDER BY object_name, column_name;
GO
How do I find all the constraints for a specified table?
USE;
GO
SELECT OBJECT_NAME(object_id) as constraint_name
,SCHEMA_NAME(schema_id) AS schema_name
,OBJECT_NAME(parent_object_id) AS table_name
,type_desc
,create_date
,modify_date
,is_ms_shipped
,is_published
,is_schema_published
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'
AND parent_object_id = OBJECT_ID('');
GO
How do I find all the indexes for a specified table?
USE;
GO
SELECT i.name AS index_name
,i.type_desc
,is_unique
,ds.type_desc AS filegroup_or_partition_scheme
,ds.name AS filegroup_or_partition_scheme_name
,ignore_dup_key
,is_primary_key
,is_unique_constraint
,fill_factor
,is_padded
,is_disabled
,allow_row_locks
,allow_page_locks
FROM sys.indexes AS i
INNER JOIN sys.data_spaces AS ds ON i.data_space_id = ds.data_space_id
WHERE is_hypothetical = 0 AND i.index_id <> 0
AND i.object_id = OBJECT_ID('');
GO
Before you run the following query, replace
USE
GO
SELECT c.name AS column_name
,c.column_id
,SCHEMA_NAME(t.schema_id) AS type_schema
,t.name AS type_name
,t.is_user_defined
,t.is_assembly_type
,c.max_length
,c.precision
,c.scale
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
WHERE c.object_id = OBJECT_ID('
ORDER BY c.column_id;
GO
How do I find all the owners of entities contained in a specified schema?
USE
GO
SELECT 'OBJECT' AS entity_type
,USER_NAME(OBJECTPROPERTY(object_id, 'OwnerId')) AS owner_name
,name
FROM sys.objects WHERE SCHEMA_NAME(schema_id) = '
UNION
SELECT 'TYPE' AS entity_type
,USER_NAME(TYPEPROPERTY(SCHEMA_NAME(schema_id) + '.' + name, 'OwnerId')) AS owner_name
,name
FROM sys.types WHERE SCHEMA_NAME(schema_id) = '
UNION
SELECT 'XML SCHEMA COLLECTION' AS entity_type
,COALESCE(USER_NAME(xsc.principal_id),USER_NAME(s.principal_id)) AS owner_name
,xsc.name
FROM sys.xml_schema_collections AS xsc JOIN sys.schemas AS s
ON s.schema_id = xsc.schema_id
WHERE s.name = '
GO
How do I find all the tables that do not have a primary key?
USE
GO
SELECT SCHEMA_NAME(t.schema_id) AS schema_name
,t.name AS table_name
FROM sys.tables t
WHERE object_id NOT IN
(
SELECT parent_object_id
FROM sys.key_constraints
WHERE type_desc = 'PRIMARY_KEY_CONSTRAINT' -- or type = 'PK'
);
GO
Or, you can run the following query.
USE
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name
,name AS table_name
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 0
ORDER BY schema_name, table_name;
GO
How do I find all the tables that do not have an index?
USE
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name
,name AS table_name
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 0
ORDER BY schema_name, table_name;
GO
How do I find all the tables that have an identity column?
USE
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name
, t.name AS table_name
, c.name AS column_name
FROM sys.tables AS t
JOIN sys.identity_columns c ON t.object_id = c.object_id
ORDER BY schema_name, table_name;
GO
Or, you can run the following query.
USE
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name
,name AS table_name
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'TableHasIdentity') = 1
ORDER BY schema_name, table_name;
GO
How do I find all the stored procedures in a database?
USE
GO
SELECT name AS procedure_name
,SCHEMA_NAME(schema_id) AS schema_name
,type_desc
,create_date
,modify_date
FROM sys.procedures;
GO
How do I find the parameters for a specified stored procedure or function?
USE
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name
,o.name AS object_name
,o.type_desc
,p.parameter_id
,p.name AS parameter_name
,TYPE_NAME(p.user_type_id) AS parameter_type
,p.max_length
,p.precision
,p.scale
,p.is_output
FROM sys.objects AS o
INNER JOIN sys.parameters AS p ON o.object_id = p.object_id
WHERE o.object_id = OBJECT_ID('
ORDER BY schema_name, object_name, p.parameter_id;
GO
How do I find all the user-defined functions in a database?
USE
GO
SELECT name AS function_name
,SCHEMA_NAME(schema_id) AS schema_name
,type_desc
,create_date
,modify_date
FROM sys.objects
WHERE type_desc LIKE '%FUNCTION%';
GO
How do I find all views in a database?
USE
GO
SELECT name AS view_name
,SCHEMA_NAME(schema_id) AS schema_name
,OBJECTPROPERTYEX(object_id,'IsIndexed') AS IsIndexed
,OBJECTPROPERTYEX(object_id,'IsIndexable') AS IsIndexable
,create_date
,modify_date
FROM sys.views;
GO
How do I find all the entities that have been modified in the last N days?
USE
GO
SELECT name AS object_name
,SCHEMA_NAME(schema_id) AS schema_name
,type_desc
,create_date
,modify_date
FROM sys.objects
WHERE modify_date > GETDATE() -
ORDER BY modify_date;
GO
How do I view the definition of a server-level trigger?
SELECT definition
FROM sys.server_sql_modules;
GO
How do I find the columns of a primary key for a specified table?
USE
GO
SELECT i.name AS index_name
,ic.index_column_id
,key_ordinal
,c.name AS column_name
,TYPE_NAME(c.user_type_id)AS column_type
,is_identity
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns AS c
ON ic.object_id = c.object_id AND c.column_id = ic.column_id
WHERE i.is_primary_key = 1
AND i.object_id = OBJECT_ID('
GO
Or, you can use the COL_NAME function as shown in the following example.
USE
GO
SELECT i.name AS index_name
,COL_NAME(ic.object_id,ic.column_id) AS column_name
,ic.index_column_id
,key_ordinal
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.object_id = ic.object_id AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1
AND i.object_id = OBJECT_ID('
GO
How do I find the columns of a foreign key for a specified table?
USE
GO
SELECT
f.name AS foreign_key_name
,OBJECT_NAME(f.parent_object_id) AS table_name
,COL_NAME(fc.parent_object_id, fc.parent_column_id) AS constraint_column_name
,OBJECT_NAME (f.referenced_object_id) AS referenced_object
,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS referenced_column_name
,is_disabled
,delete_referential_action_desc
,update_referential_action_desc
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.object_id = fc.constraint_object_id
WHERE f.parent_object_id = OBJECT_ID('
How do I determine if a column is used in a computed column expression?
USE
GO
SELECT OBJECT_NAME(object_id) AS object_name
,COL_NAME(object_id, column_id) AS computed_column
,class_desc
,is_selected
,is_updated
,is_select_all
FROM sys.sql_dependencies
WHERE referenced_major_id = OBJECT_ID('
AND referenced_minor_id = COLUMNPROPERTY(referenced_major_id, '
AND class = 1;
GO
How do I find all the columns that are used in a computed column expression?
USE
GO
SELECT OBJECT_NAME(d.referenced_major_id) AS object_name
,COL_NAME(d.referenced_major_id, d.referenced_minor_id) AS column_name
,OBJECT_NAME(referenced_major_id) AS dependent_object_name
,COL_NAME(d.object_id, d.column_id) AS dependent_computed_column
,cc.definition AS computed_column_definition
FROM sys.sql_dependencies AS d
JOIN sys.computed_columns AS cc
ON cc.object_id = d.object_id AND cc.column_id = d.column_id AND d.object_id=d.referenced_major_id
WHERE d.class = 1
ORDER BY object_name, column_name;
GO
How do I find all the constraints for a specified table?
USE
GO
SELECT OBJECT_NAME(object_id) as constraint_name
,SCHEMA_NAME(schema_id) AS schema_name
,OBJECT_NAME(parent_object_id) AS table_name
,type_desc
,create_date
,modify_date
,is_ms_shipped
,is_published
,is_schema_published
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'
AND parent_object_id = OBJECT_ID('
GO
How do I find all the indexes for a specified table?
USE
GO
SELECT i.name AS index_name
,i.type_desc
,is_unique
,ds.type_desc AS filegroup_or_partition_scheme
,ds.name AS filegroup_or_partition_scheme_name
,ignore_dup_key
,is_primary_key
,is_unique_constraint
,fill_factor
,is_padded
,is_disabled
,allow_row_locks
,allow_page_locks
FROM sys.indexes AS i
INNER JOIN sys.data_spaces AS ds ON i.data_space_id = ds.data_space_id
WHERE is_hypothetical = 0 AND i.index_id <> 0
AND i.object_id = OBJECT_ID('
GO
Friday, February 25, 2011
Information related to the security admin server role and system admin server role
Security is a very important aspect that a DBA should know about. It is also important to know which login has a sysadmin or security admin server level role. The following SQL Command will show information related to the security admin server role and system admin server role.
SELECT l.name, l.denylogin, l.isntname, l.isntgroup, l.isntuser
FROM master.dbo.syslogins l
WHERE l.sysadmin = 1 OR l.securityadmin = 1
SELECT l.name, l.denylogin, l.isntname, l.isntgroup, l.isntuser
FROM master.dbo.syslogins l
WHERE l.sysadmin = 1 OR l.securityadmin = 1
SERVERPROPERTY
The following T-SQL statement retrieves information such as Hostname, Current instance name, Edition, Server type, ServicePack and version number from current SQL Server connection. 'Edition' will give information on a 32 bit or 64 bit architecture and 'Productlevel' gives information about what service pack your SQL Server is on. It also displays if the current SQL Server is a clustered server.
SELECT
SERVERPROPERTY('MachineName') as Host,
SERVERPROPERTY('InstanceName') as Instance,
SERVERPROPERTY('Edition') as Edition, /*shows 32 bit or 64 bit*/
SERVERPROPERTY('ProductLevel') as ProductLevel, /* RTM or SP1 etc*/
Case SERVERPROPERTY('IsClustered') when 1 then 'CLUSTERED' else
'STANDALONE' end as ServerType,
@@VERSION as VersionNumber
SELECT
SERVERPROPERTY('MachineName') as Host,
SERVERPROPERTY('InstanceName') as Instance,
SERVERPROPERTY('Edition') as Edition, /*shows 32 bit or 64 bit*/
SERVERPROPERTY('ProductLevel') as ProductLevel, /* RTM or SP1 etc*/
Case SERVERPROPERTY('IsClustered') when 1 then 'CLUSTERED' else
'STANDALONE' end as ServerType,
@@VERSION as VersionNumber
Thursday, February 24, 2011
Kill ALL Connections To a SQL Database
The Kill Connection script utilizes sys.dm_tran_locks (syslockinfo for SQL 2000) to capture SPIDs that are accessing the specified database. The traditional way of using sysprocesses table alone can not detect distributed transactions or cross-database references to the database.
Create Proc [dbo].[usp_killConnections]
@db_name Nvarchar(200)
AS
set nocount on
if db_id(@db_name) is null
return
declare @spid int
declare spid cursor for
/* For SQL 2000 and SQL 2005/2008 Backward compatible mode*/
select spid from master.dbo.sysprocesses(nolock) where dbid = db_id(@db_name) and spid > 50
union
select distinct req_spid from sys.syslockinfo(nolock) where rsc_dbid = db_id(@db_name) and req_spid > 50
/* For SQL 2005/2008 */
/*
select spid from master.dbo.sysprocesses(nolock) where dbid = db_id(@db_name) and spid > 50
union
select distinct request_session_id from sys.dm_tran_locks (nolock) where resource_database_id = db_id(@db_name) and request_session_id > 50
*/
open spid
fetch next from spid
into @spid
while @@fetch_status = 0
begin
exec ('kill ' + @spid)
fetch next from spid into @spid
end
close spid
deallocate spid
Create Proc [dbo].[usp_killConnections]
@db_name Nvarchar(200)
AS
set nocount on
if db_id(@db_name) is null
return
declare @spid int
declare spid cursor for
/* For SQL 2000 and SQL 2005/2008 Backward compatible mode*/
select spid from master.dbo.sysprocesses(nolock) where dbid = db_id(@db_name) and spid > 50
union
select distinct req_spid from sys.syslockinfo(nolock) where rsc_dbid = db_id(@db_name) and req_spid > 50
/* For SQL 2005/2008 */
/*
select spid from master.dbo.sysprocesses(nolock) where dbid = db_id(@db_name) and spid > 50
union
select distinct request_session_id from sys.dm_tran_locks (nolock) where resource_database_id = db_id(@db_name) and request_session_id > 50
*/
open spid
fetch next from spid
into @spid
while @@fetch_status = 0
begin
exec ('kill ' + @spid)
fetch next from spid into @spid
end
close spid
deallocate spid
String tokenizing / splitting
Simply copy and paste the Split() function code in your T-SQL code editor, to select appropriate DB from database combo box and then press F5 key to create user defined Split() function.
Now you can use it as follows:
1. The first parameter of function is string that you need to split.
2. And second parameter of function is delimiter.
SELECT * FROM dbo.Split ('Token1;Token2;Token3;Token4;Token5',';')
The Expected output will be as follow:
Token1
Token2
Token3
Token4
Token5
CREATE FUNCTION dbo.Split
(
@String VARCHAR(8000),
@Delimiter NVARCHAR(1)
)
RETURNS @Tokens table (Token NVARCHAR(255))
AS
BEGIN
WHILE (CHARINDEX(@Delimiter,@String)>0)
BEGIN
INSERT INTO @Tokens (Token) VALUES
(LTRIM(RTRIM(SUBSTRING(@String,1,CHARINDEX(@Delimiter,@String)-1))))
SET @String = SUBSTRING(@String, CHARINDEX(@Delimiter,@String)+LEN(@Delimiter),LEN(@String))
END
INSERT INTO @Tokens (Token) VALUES (LTRIM(RTRIM(@String)))
RETURN
END
Now you can use it as follows:
1. The first parameter of function is string that you need to split.
2. And second parameter of function is delimiter.
SELECT * FROM dbo.Split ('Token1;Token2;Token3;Token4;Token5',';')
The Expected output will be as follow:
Token1
Token2
Token3
Token4
Token5
CREATE FUNCTION dbo.Split
(
@String VARCHAR(8000),
@Delimiter NVARCHAR(1)
)
RETURNS @Tokens table (Token NVARCHAR(255))
AS
BEGIN
WHILE (CHARINDEX(@Delimiter,@String)>0)
BEGIN
INSERT INTO @Tokens (Token) VALUES
(LTRIM(RTRIM(SUBSTRING(@String,1,CHARINDEX(@Delimiter,@String)-1))))
SET @String = SUBSTRING(@String, CHARINDEX(@Delimiter,@String)+LEN(@Delimiter),LEN(@String))
END
INSERT INTO @Tokens (Token) VALUES (LTRIM(RTRIM(@String)))
RETURN
END
Unused indexes in your databases
This Script allows you to determine the list of unused indexes in your databases
select object_name (i.object_id) as NomTable,isnull( i.name,'HEAP') as IndexName
from sys.objects o inner join sys.indexes i
ON i.[object_id] = o.[object_id] left join
sys.dm_db_index_usage_stats s
on i.index_id = s.index_id and s.object_id = i.object_id
where object_name (o.object_id) is not null
and object_name (s.object_id) is null
AND o.[type] = 'U'
and isnull( i.name,'HEAP') <>'HEAP'
union all
select object_name (i.object_id) as NomTable,isnull( i.name,'HEAP') as IndexName
from sys.objects o inner join sys.indexes i
ON i.[object_id] = o.[object_id] left join
sys.dm_db_index_usage_stats s
on i.index_id = s.index_id and s.object_id = i.object_id
where user_seeks= 0 and user_scans=0 and user_lookups= 0 AND o.[type] = 'U'
and isnull( i.name,'HEAP') <>'HEAP'
order by NomTable asc
order by OBJECT_NAME(i.object_id) ASC
select type,* from sys.objects
where name = 'DDAOeuvre'
where user_seeks=0 and user_scans=0 and user_lookups = 0
order by object_name (s.object_id) asc
select object_name (i.object_id) as NomTable,isnull( i.name,'HEAP') as IndexName
from sys.objects o inner join sys.indexes i
ON i.[object_id] = o.[object_id] left join
sys.dm_db_index_usage_stats s
on i.index_id = s.index_id and s.object_id = i.object_id
where object_name (o.object_id) is not null
and object_name (s.object_id) is null
AND o.[type] = 'U'
and isnull( i.name,'HEAP') <>'HEAP'
union all
select object_name (i.object_id) as NomTable,isnull( i.name,'HEAP') as IndexName
from sys.objects o inner join sys.indexes i
ON i.[object_id] = o.[object_id] left join
sys.dm_db_index_usage_stats s
on i.index_id = s.index_id and s.object_id = i.object_id
where user_seeks= 0 and user_scans=0 and user_lookups= 0 AND o.[type] = 'U'
and isnull( i.name,'HEAP') <>'HEAP'
order by NomTable asc
order by OBJECT_NAME(i.object_id) ASC
select type,* from sys.objects
where name = 'DDAOeuvre'
where user_seeks=0 and user_scans=0 and user_lookups = 0
order by object_name (s.object_id) asc
ROW COUNT
SELECT o.name AS "Table Name", i.rowcnt AS "Row Count"
FROM sysobjects o, sysindexes i
WHERE i.id = o.id AND indid IN(0,1)
AND xtype = 'u'
AND o.name <> 'sysdiagrams'
ORDER BY i.rowcnt DESC
FROM sysobjects o, sysindexes i
WHERE i.id = o.id AND indid IN(0,1)
AND xtype = 'u'
AND o.name <> 'sysdiagrams'
ORDER BY i.rowcnt DESC
Searching through SPs, Tables, Views
This little procedure is very useful during development / support to quickly find all objects that may have something to do with a certain term, column name, part of thereof and so on.
CREATE PROCEDURE adhoc_SearchText(@text VARCHAR(1024))
AS
BEGIN
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES T
WHERE charindex(@text, T.TABLE_NAME)>0
SELECT C.TABLE_NAME, C.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE charindex(@text, C.COLUMN_NAME)>0
SELECT V.TABLE_NAME AS VIEW_NAME
FROM information_schema.VIEWS V
WHERE charindex(@text, V.VIEW_DEFINITION)>0
SELECT R.ROUTINE_NAME
FROM information_schema.routines r
WHERE charindex(@text, r.ROUTINE_DEFINITION)>0
END
CREATE PROCEDURE adhoc_SearchText(@text VARCHAR(1024))
AS
BEGIN
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES T
WHERE charindex(@text, T.TABLE_NAME)>0
SELECT C.TABLE_NAME, C.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE charindex(@text, C.COLUMN_NAME)>0
SELECT V.TABLE_NAME AS VIEW_NAME
FROM information_schema.VIEWS V
WHERE charindex(@text, V.VIEW_DEFINITION)>0
SELECT R.ROUTINE_NAME
FROM information_schema.routines r
WHERE charindex(@text, r.ROUTINE_DEFINITION)>0
END
Wednesday, February 23, 2011
Get the Table definition from Database
Table definition from SQL server tables
sp_help --(followed by a table name)
ex: sp_help EMP
Table definition from Oracle tables
desc --(followed by a table name)
ex: desc EMP
sp_help --(followed by a table name)
ex: sp_help EMP
Table definition from Oracle tables
desc --(followed by a table name)
ex: desc EMP
Thursday, February 17, 2011
AVOID CURSORS
If possible, avoid using SQL Server cursors. They generally use a lot of SQL Server resources and reduce the performance and scalability of your applications. If you need to perform row-by-row operations, try to find another method to perform the task.
Here are some alternatives to using a cursor:
• Use WHILE LOOPS
• Use temp tables
• Use derived tables
• Use correlated sub-queries
• Use the CASE statement
• Perform multiple queries
If you do find you must use a cursor, try to reduce the number of records to process.
One way to do this is to move the records that need to be processed into a temp table first, and then create the cursor to use the records in the temp table, not from the original table. This of course assumes that the subsets of records to be inserted into the temp table are substantially less than those in the original table.
The lower the number of records to process, the faster the cursor will finish.
If you have no choice but to use a server-side cursor in your application, try to use a FORWARD-ONLY or FAST-FORWARD, READ-ONLY cursor. When working with unidirectional, read-only data, use the FAST_FORWARD option instead of the FORWARD_ONLY option, as it has some internal performance optimizations to speed performance. This type of cursor produces the least amount of overhead on SQL Server.
If you are unable to use a fast-forward cursor, then try the following cursors in this order, until you find one that meets your needs. They are listed in the order of their performance characteristics, from fastest to slowest: dynamic, static, and keyset.
Avoid using static/insensitive and keyset cursors, unless you have no other choice. This is because they cause a temporary table to be created in TEMPDB, which increases overhead and can cause resource contention issues.
If you have no choice but to use cursors in your application, try to locate the SQL Server tempdb database on its own physical device for best performance. This is because cursors use the tempdb for temporary storage of cursor data. The faster your disk array running tempdb, the faster your cursor will be.
Using cursors can reduce concurrency and lead to unnecessary locking and blocking. To help avoid this, use the READ_ONLY cursor option if applicable, or if you need to perform updates, try to use the OPTIMISTIC cursor option to reduce locking. Try to avoid the SCROLL_LOCKS cursor option, which reduces concurrency.
When you are done using a cursor, don't just CLOSE it, you must also DEALLOCATE it. Deallocation is required to free up the SQL Server resources used by the cursor. If you only CLOSE the cursor, locks are freed, but SQL Server resources are not. If you don't DEALLOCATE your cursors, the resources used by the cursor will stay allocated, degrading the performance of your server until they are released.
If it is appropriate for your application, try to load the cursor as soon as possible by moving to the last row of the result set. This releases the share locks created when the cursor was built, freeing up SQL Server resources.
If you have to use a cursor because your application needs to manually scroll through records and update them, try to avoid client-side cursors, unless the number of rows is small or the data is static. If the number of rows is large, or the data is not static, consider using a server-side keyset cursor instead of a client-side cursor. Performance is usually boosted because of a reduction in network traffic between the client and the server. For optimum performance, you may have to try both types of cursors under realistic loads to determine which is best for your particular environment.
When using a server-side cursor, always try to fetch as small a result set as possible. This includes fetching only those rows and columns the client needs immediately. The smaller the cursor, no matter what type of server-side cursor it is, the fewer resources it will use, and performance will benefit.
If you need to perform a JOIN as part of your cursor, keyset and static cursors are generally faster than dynamic cursors, and should be used when possible.
Here are some alternatives to using a cursor:
• Use WHILE LOOPS
• Use temp tables
• Use derived tables
• Use correlated sub-queries
• Use the CASE statement
• Perform multiple queries
If you do find you must use a cursor, try to reduce the number of records to process.
One way to do this is to move the records that need to be processed into a temp table first, and then create the cursor to use the records in the temp table, not from the original table. This of course assumes that the subsets of records to be inserted into the temp table are substantially less than those in the original table.
The lower the number of records to process, the faster the cursor will finish.
If you have no choice but to use a server-side cursor in your application, try to use a FORWARD-ONLY or FAST-FORWARD, READ-ONLY cursor. When working with unidirectional, read-only data, use the FAST_FORWARD option instead of the FORWARD_ONLY option, as it has some internal performance optimizations to speed performance. This type of cursor produces the least amount of overhead on SQL Server.
If you are unable to use a fast-forward cursor, then try the following cursors in this order, until you find one that meets your needs. They are listed in the order of their performance characteristics, from fastest to slowest: dynamic, static, and keyset.
Avoid using static/insensitive and keyset cursors, unless you have no other choice. This is because they cause a temporary table to be created in TEMPDB, which increases overhead and can cause resource contention issues.
If you have no choice but to use cursors in your application, try to locate the SQL Server tempdb database on its own physical device for best performance. This is because cursors use the tempdb for temporary storage of cursor data. The faster your disk array running tempdb, the faster your cursor will be.
Using cursors can reduce concurrency and lead to unnecessary locking and blocking. To help avoid this, use the READ_ONLY cursor option if applicable, or if you need to perform updates, try to use the OPTIMISTIC cursor option to reduce locking. Try to avoid the SCROLL_LOCKS cursor option, which reduces concurrency.
When you are done using a cursor, don't just CLOSE it, you must also DEALLOCATE it. Deallocation is required to free up the SQL Server resources used by the cursor. If you only CLOSE the cursor, locks are freed, but SQL Server resources are not. If you don't DEALLOCATE your cursors, the resources used by the cursor will stay allocated, degrading the performance of your server until they are released.
If it is appropriate for your application, try to load the cursor as soon as possible by moving to the last row of the result set. This releases the share locks created when the cursor was built, freeing up SQL Server resources.
If you have to use a cursor because your application needs to manually scroll through records and update them, try to avoid client-side cursors, unless the number of rows is small or the data is static. If the number of rows is large, or the data is not static, consider using a server-side keyset cursor instead of a client-side cursor. Performance is usually boosted because of a reduction in network traffic between the client and the server. For optimum performance, you may have to try both types of cursors under realistic loads to determine which is best for your particular environment.
When using a server-side cursor, always try to fetch as small a result set as possible. This includes fetching only those rows and columns the client needs immediately. The smaller the cursor, no matter what type of server-side cursor it is, the fewer resources it will use, and performance will benefit.
If you need to perform a JOIN as part of your cursor, keyset and static cursors are generally faster than dynamic cursors, and should be used when possible.
SQL JOIN
SQL JOIN
The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables.
Tables in a database are often related to each other with keys.
A primary key is a column (or a combination of columns) with a unique value for each row. Each primary key value must be unique within the table. The purpose is to bind data together, across tables, without repeating all of the data in every table.
Different SQL JOINs
JOIN: Return rows when there is at least one match in both tables
LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
FULL JOIN: Return rows when there is a match in one of the tables
The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables.
Tables in a database are often related to each other with keys.
A primary key is a column (or a combination of columns) with a unique value for each row. Each primary key value must be unique within the table. The purpose is to bind data together, across tables, without repeating all of the data in every table.
Different SQL JOINs
JOIN: Return rows when there is at least one match in both tables
LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
FULL JOIN: Return rows when there is a match in one of the tables
Thursday, February 10, 2011
SQL SERVER – Quickest Way to – Kill All Threads – Kill All User Session – Kill All Processes
I found the quickest way to kill the processes of SQL Server.
USE master;
GO
ALTER DATABASE AdventureWorks
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
ALTER DATABASE AdventureWorks
SET MULTI_USER;
GO
Running above script will give following result.
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
USE master;
GO
ALTER DATABASE AdventureWorks
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
ALTER DATABASE AdventureWorks
SET MULTI_USER;
GO
Running above script will give following result.
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
Advanced Query Tool
Cross-database query and admin tool. Powerful, fast, inexpensive.
www.querytool.com
www.querytool.com
SQL Good Practices
1)Avoid use of SELECT * in SQL queries. Instead practice writing required column names after SELECT statement.
Example:
SELECT Username, Password
FROM UserDetails
2)Avoid using temporary tables and derived tables as it uses more disks I/O. Instead use CTE (Common Table Expression); its scope is limited to the next statement in SQL query.
3)Use SET NOCOUNT ON at the beginning of SQL Batches, Stored Procedures and Triggers. This improves the performance of Stored Procedure.
4)Practice using PRIMARY key in WHERE condition of UPDATE or DELETE statements as this will avoid error possibilities.
5)Instead of using LOOP to insert data from Table B to Table A, try to use SELECT statement with INSERT statement.
INSERT INTO TABLE A (column1, column2)
SELECT column1, column2
FROM TABLE B
WHERE ....
6)Do not use the RECOMPILE option for Stored Procedure as it reduces the performance.
7)Always put the DECLARE statements at the starting of the code in the stored procedure. This will make the query optimizer to reuse query plans.
8)Put the SET statements in beginning (after DECLARE) before executing code in the stored procedure.
9)Use BEGIN…END blocks only when multiple statements are present within a conditional code segment.
Example:
SELECT Username, Password
FROM UserDetails
2)Avoid using temporary tables and derived tables as it uses more disks I/O. Instead use CTE (Common Table Expression); its scope is limited to the next statement in SQL query.
3)Use SET NOCOUNT ON at the beginning of SQL Batches, Stored Procedures and Triggers. This improves the performance of Stored Procedure.
4)Practice using PRIMARY key in WHERE condition of UPDATE or DELETE statements as this will avoid error possibilities.
5)Instead of using LOOP to insert data from Table B to Table A, try to use SELECT statement with INSERT statement.
INSERT INTO TABLE A (column1, column2)
SELECT column1, column2
FROM TABLE B
WHERE ....
6)Do not use the RECOMPILE option for Stored Procedure as it reduces the performance.
7)Always put the DECLARE statements at the starting of the code in the stored procedure. This will make the query optimizer to reuse query plans.
8)Put the SET statements in beginning (after DECLARE) before executing code in the stored procedure.
9)Use BEGIN…END blocks only when multiple statements are present within a conditional code segment.
Subscribe to:
Posts (Atom)