Client Statistics

Number of server roundtrips – a roundtrip consists of a request sent to the server and a reply from the server to the client.

TDS (= tabular data stream) Packets sent from the client – TDS is the language which SQL Server speaks, and in order for applications to communicate with SQL Server, they need to pack the requests in TDS packets. TDS Packets sent from the client is the number of packets sent from the client; in case the request is large, then it may need more buffers, and eventually might even need more server roundtrips.

TDS packets received from server – the TDS packets sent by the server to the client during the query execution.

Bytes sent from client – the volume of the data set to our SQL Server, measured in bytes; i.e. how big of a query we have sent to the SQL Server.

Bytes received from server – the amount of data the SQL Server has sent to the client, measured in bytes. Depending on the number of rows and the datatypes involved, this number will vary.

Client processing time – the amount of time spent in milliseconds between the first received response packet and the last received response packet by the client.

Wait time on server replies – the time in milliseconds between the last request packet which left the client and the first response packet which came back from the server to the client.

Total execution time – the sum of client processing time and wait time on server replies (the SQL Server internal processing time).

Show/hide Client Statistics:

Sets stored procedure for auto execution at SQL Server Startup

sp_procoption [ @ProcName = ] 'procedure' -- the name of the procedure marked for auto-execution

    , [ @OptionName = ] 'STARTUP' --The only value for option is startup.

    , [ @OptionValue = ] 'value'  -- on/off 

Notes:

Startup procedures must be in the master database.

Startup procedures can't contain INPUT or OUTPUT parameters. 

Execution of the stored procedures starts when the master database is recovered at startup.

Requires membership in the sysadmin fixed server role.

http://msdn.microsoft.com/en-us/library/ms181720.aspx

Convert datetime<-->nvarchar

CONVERT(DATETIME,ExpiryDate,103) 

Date format styles:

http://msdn.microsoft.com/en-us/library/ms187928.aspx

SQL Constraints creation

UNIQUE Constraint

ALTER TABLE [TABLENAME] ADD CONSTRAINT [CONSTRAINTNAME] UNIQUE([COLUMNNAME])

CHECK Constraint

ALTER TABLE [TABLENAME] WITH NOCHECK ADD CONSTRAINT [CONSTRAINTNAME] CHECK  (([COLUMNNAME] IS NOT NULL))

DEFAULT constraint

ALTER TABLE [TABLENAME] ADD CONSTRAINT [CONSTRAINTNAME] DEFAULT (CONVERT([datetime],CONVERT([varchar],getdate(),(101)),(0))) FOR [COLUMNNAME]

NOT NULL constraint

ALTER TABLE [TABLENAME] ALTER COLUMN [COLUMNNAME] <data_type> NOT NULL

FOREIGN KEY constraint

ALTER TABLE [TABLENAME] WITH CHECK ADD CONSTRAINT [CONSTRAINTNAME] FOREIGN KEY([COLUMNNAME])

REFERENCES [PrimaryTableName] ([PKcolumn])

PRIMARY KEY constraint

Specify fields that uniquely identify each record in the table. 

Enable/Disable constraints on table

-- Disable all constraint on table

Alter table [table_name] nocheck constraint all

-- Enable all constraint on table:

Alter table [table_name] check constraint all -- do not check existing data

Alter table [table_name] with check check constraint all -- check existing data

On Delete/Update cascade

Corresponding rows are deleted/updated in the referencing table when that row is updated in the parent table.

When rows in the PK (referenced) table are deleted/updated, the respective rows of the FK (referencing) table with a matching foreign key column will get deleted/updated as well.

Alter Table TableFK

Add Constraint FK_CascadeExample Foreign Key(ID)

References TablePK(ID) On Delete Cascade On Update Cascade;

inserted/deleted with text/ntext/image in SQL Server triggers

Error obtained when using text, ntext, or image columns of INSERTED/DELETED tables (in triggers):

Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.

Solutions:

1. Get the text field from the table itself, joining it to the INSERTED table on the primary key.

2. Use INSTEAD OF triggers.

Pending IO requests

SELECT vfs.database_id, df.name, df.physical_name, vfs.FILE_ID, ior.io_pending

FROM sys.dm_io_pending_io_requests ior

INNER JOIN sys.dm_io_virtual_file_stats (DB_ID(), NULL) vfs ON vfs.file_handle = ior.io_handle

INNER JOIN sys.database_files df ON df.FILE_ID = vfs.FILE_ID

CDC - Change Data Capture

http://blog.sqlauthority.com/2009/08/15/sql-server-introduction-to-change-data-capture-cdc-in-sql-server-2008/

http://msdn.microsoft.com/en-us/library/cc645937.aspx

http://www.codeproject.com/KB/database/MSSQLServer_CDC.aspx

http://www.simple-talk.com/sql/learn-sql-server/introduction-to-change-data-capture-%28cdc%29-in-sql-server-2008/

http://www.sqlserver.co.il/?p=480

Business Keys

Business Key columns identify one or more columns that the SCD component can use to determine how Existing Dimension rows get matched up to Source Systemrows, in order to determine if any changes have occurred in the source system.

http://support.sas.com/documentation/cdl/en/etlug/62233/HTML/default/n03673ddo0y2twn114ephiuc8s5o.htm

Read about Slowly Changing Dimensions: 

http://copypastenet.blogspot.com/2011/03/slowly-changing-dimensions.html

SCD - Slowly Changing Dimensions

Slowly Changing Dimensions 

http://www.1keydata.com/datawarehousing/scd.html

http://gerireshef.wordpress.com/2010/01/29/%D7%90%D7%99%D7%AA%D7%95%D7%A8-%D7%A9%D7%92%D7%99%D7%90%D7%95%D7%AA-%D7%91%D7%98%D7%91%D7%9C%D7%90%D7%95%D7%AA-slowly-changing-dimension/

http://msdn.microsoft.com/en-us/library/ms141715.aspx

http://www.geekinterview.com/question_details/229

Slowly Changing Dimension in SSIS

http://netindonesia.net/blogs/kasim.wirama/archive/2007/10/25/slowly-changing-dimension-in-ssis.aspx

Kimball Method SSIS Slowly Changing Dimension Component

http://kimballscd.codeplex.com/


Business Keys
http://copypastenet.blogspot.com/2011/03/business-keys.html

A script to check the usage of table in the DB

declare @TableName nvarchar(100)

select @TableName = 'TableName'

----------------------------------------------------------

-- get data about the table using sp_help:

----------------------------------------------------------

declare @query nvarchar(100)

SET @query = 'sp_help ''dbo.' + @TableName + ''''

exec (@query)

----------------------------------------------------------

-- table triggers:

----------------------------------------------------------

select name as triggerName, OBJECT_NAME(parent_object_id) from sys.objects 

where type = 'TR' and SCHEMA_NAME(schema_id) = 'dbo'

and OBJECT_NAME(parent_object_id) = @TableName

order by name

----------------------------------------------------------

-- foreign keys constraints from other tables to @TableName :

----------------------------------------------------------

select  object_name(f.constraint_object_id) as NameofConstraint,

SCHEMA_NAME(o.schema_id) AS SchemaName,

object_name(f.parent_object_id) AS TableName,

type_desc AS ConstraintType

from sys.foreign_key_columns f

inner join sys.objects o on o.object_id = f.constraint_object_id  

and SCHEMA_NAME(o.schema_id) = 'dbo'

where object_name(f.referenced_object_id) = @TableName

order by object_name(f.constraint_object_id)

----------------------------------------------------------

-- get dependencies

----------------------------------------------------------

select distinct sd.id, so.name, so.Type, sd.depid, so_dep.name, so_dep.Type

from  sys.sysdepends sd

inner join sys.objects so on so.object_id = sd.id 

and SCHEMA_NAME(so.schema_id) = 'dbo'

inner join sys.objects so_dep on so_dep.object_id = sd.depid 

where so_dep.name = @TableName

order by so.name

----------------------------------------------------------

--select * from table

----------------------------------------------------------

SET @query = 'select * from dbo.' + @TableName

exec (@query)

Synchronize two tables using Merge command

Merge - performs insert, update, or delete operations on a target table based on the results of a join with a source table.

MERGE <target_table> [AS TARGET]

USING <table_source> [AS SOURCE]
ON <search_condition>
[WHEN MATCHED –exists in the 2 tables (update)
THEN <merge_matched> ]
[WHEN NOT MATCHED [BY TARGET] –not exists in the target table (insert)
THEN <merge_not_matched> ]
[WHEN NOT MATCHED BY SOURCE –not exists in the source table (delete)

THEN <merge_ matched> ];

http://technet.microsoft.com/en-us/library/bb510625.aspx

http://www.mssqltips.com/tip.asp?tip=1704

http://gerireshef.wordpress.com/2010/06/07/%D7%A9%D7%99%D7%9E%D7%95%D7%A9-%D7%91%D7%A4%D7%A7%D7%95%D7%93%D7%AA-merge-%D7%9C%D7%A2%D7%93%D7%9B%D7%95%D7%9F-%D7%98%D7%91%D7%9C%D7%AA-slowly-changing-dimension/?blogsub=confirming#subscribe-blog

Remove permissions of table from user

REVOKE DELETE ON TABLENAME FROM sss

Notes:

• Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.
• The user could have implicit rights through a stored procedure, so setting only the permissions at table level could not be enough.

Could not find an object from another schema

When try to call to object without write the specific schema:

• dbo/db_owner/sysadmin doesn't find an object from another schema - because dbo/db_owner/sysadmin doesn't try to find objects in other schemas, Unless another schema is written Explicitly.
• Other Users/Schemas/Rools try to find the objects in their schema, and if it's not exists - they will try to find the objects in dbo schema.

http://social.technet.microsoft.com/Forums/he-IL/sqlhe/thread/9e3b2677-602c-4697-a0de-292929ebda54

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint. The conflict occurred in database , table , column

Error message:

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint. The conflict occurred in database , table , column

Explanation:

Some foreign values do not exist in primary table.

Solution:

Make your table empty, or add the associated value to the primary key table.

A query to find the missing values:

SELECT F.* FROM ForeignTableName F

LEFT JOIN PrimaryTableName P on F.FKcolumn = P.PKcolumn

WHERE P.PKcolumn is null

Search for a text including special characters in SQL Server

1. Delimits the special character with square brackets:

Examples:

for '%' we will write:

SELECT * FROM TableName WHERE ColumnName LIKE '%[%]%'

for the text 'UPDATE [TABLENAME]' , that already contain square brackets, we will delimit the open bracket:

select o.name from ..... where text like '%UPDATE [[]TABLENAME]%'

2. Use a custom escape character:

Examples:

for '%' we will write:

SELECT * FROM TableName WHERE ColumnName LIKE '%\%%' ESCAPE '\'

and for 'UPDATE [TABLENAME]' we will write:

select o.name from ..... where text like '%\UPDATE [TABLENAME]%' ESCAPE '\'

Note: you can protect the code from SQL Injection using ESCAPE: 

http://copypastenet.blogspot.com/2010/11/protect-from-sql-injection.html

IF UPDATE

IF UPDATE (ColumnName)

Uses in Triggers: return True if the column was updated.

Note: An update means that the query has SET the value of the column:

UPDATE TableName SET ColumnName = ColumnName --> UPDATE (ColumnName)=TRUE

UPDATE TableName SET ColumnName = 10 --when ColumnName already =10 --> UPDATE (ColumnName)=TRUE

Get Dependencies of a table

select distinct sd.id, so.name, so.Type, sd.depid, so_dep.name, so_dep.Type

from  sys.sysdepends sd

inner join sys.objects so on so.object_id = sd.id 

and SCHEMA_NAME(so.schema_id) = 'dbo'

inner join sys.objects so_dep on so_dep.object_id = sd.depid 

where so_dep.name = 'TABLENAME'

order by so.name

Understanding SQL Dependencies:

http://msdn.microsoft.com/en-us/library/ms345449.aspx

Select the last IDENTITY

SELECT @@IDENTITY will return the last identity value entered into a table in your current session.

SELECT SCOPE_IDENTITY() will return the last identity value created in the current session, in the current scope.

SELECT IDENT_CURRENT(‘TABLENAME’) will return the last identity value entered into tablename in your current session, and not only in the current scope.

When using @@IDENTITY, situation that another user will insert newer identity than us can occured, and this can cause us some problems...

@@IDENTITY - current session.

SCOPE_IDENTITY() - current session + current scope.

IDENT_CURRENT(‘TABLENAME’) - current session + specific table.

Select triggers of a table

SELECT name,OBJECT_NAME(parent_object_id) 

FROM sys.objects 

WHERE type = 'TR' 

AND SCHEMA_NAME(schema_id) = 'dbo'

AND OBJECT_NAME(parent_object_id) = 'TABLENAME'

List of tables with data

SELECT SCHEMA_NAME(schema_id) as SchemaName, 

so.name as TableName, 

MAX(si.rows) as Row_Count

FROM sys.objects so 

INNER JOIN sysindexes si on si.id = so.object_id 

WHERE so.type = 'U' 

GROUP BY so.object_id, so.name, so.schema_id

HAVING MAX(si.rows) > 0