CopyPaste.Net: July 2011

You are connected to SQL Server 2008 Edition and above - even if you work with SSMS 2008 - there is no IntelliSense to MSSQL Editions under 2008.
Check that Intellisense is enabled, look at Query-->Intellisense Enabled, or at Tools-->Options-->Text Editor-->Transact-SQL-->Intellisense, and check there that the 'Enable Intellisense' checkbox is checked.
Maybe IntelliSense should be refreshed with the latest changes. When restart the SSMS, IntelliSense is refreshed. In order to refresh it while working, go to Edit-->IntelliSense-->Refresh Local Cache.
If one or more databases is Offline - IntelliSense is not work for all of the databases in the server.

ACID

ACID is a set of properties that guarantee database transactions are processed reliably:

Atomicity,
Consistency,
Isolation,
Durability.

http://en.wikipedia.org/wiki/ACID

But, CAP theorem states that it is impossible for a distributed computer system to simultaneously provide all three of the following guarantees:

Consistency,
Availability,
Partition tolerance.

Boolean 'NOT' in SQL

DECLARE @Bit bit = 0

SET @Bit = ~@Bit

SELECT @Bit --1

SERVERPROPERTY - information about the server instance in SQL Server

SERVERPROPERTY (PropertyName)

Returns property information about the server instance in SQL Server 2008 R2.

See the properties list in:

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

Example:

SELECT

SERVERPROPERTY('servername') AS servername,

SERVERPROPERTY('Edition') AS Edition,

SERVERPROPERTY('IsSingleUser') AS IsSingleUser

Select all properties of SERVERPROPERTY():

SELECT

SERVERPROPERTY('BuildClrVersion') as BuildClrVersion,

SERVERPROPERTY('Collation') as Collation,

SERVERPROPERTY('CollationID') as CollationID,

SERVERPROPERTY('ComparisonStyle') as ComparisonStyle,

SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as ComputerNamePhysicalNetBIOS,

SERVERPROPERTY('Edition') as Edition,

SERVERPROPERTY('EditionID') as EditionID,

SERVERPROPERTY('EngineEdition') as EngineEdition,

SERVERPROPERTY('FilestreamConfiguredLevel') as FilestreamConfiguredLevel,

SERVERPROPERTY('FilestreamEffectiveLevel') as FilestreamEffectiveLevel,

SERVERPROPERTY('FilestreamShareName') as FilestreamShareName,

SERVERPROPERTY('HadrManagerStatus') as HadrManagerStatus,

SERVERPROPERTY('InstanceDefaultBackupPath') as InstanceDefaultBackupPath,

SERVERPROPERTY('InstanceDefaultDataPath') as InstanceDefaultDataPath,

SERVERPROPERTY('InstanceDefaultLogPath') as InstanceDefaultLogPath,

SERVERPROPERTY('InstanceName') as InstanceName,

SERVERPROPERTY('IsAdvancedAnalyticsInstalled') as IsAdvancedAnalyticsInstalled,

SERVERPROPERTY('IsBigDataCluster') as IsBigDataCluster,

SERVERPROPERTY('IsClustered') as IsClustered,

SERVERPROPERTY('IsFullTextInstalled') as IsFullTextInstalled,

SERVERPROPERTY('IsHadrEnabled') as IsHadrEnabled,

SERVERPROPERTY('IsIntegratedSecurityOnly') as IsIntegratedSecurityOnly,

SERVERPROPERTY('IsLocalDB') as IsLocalDB,

SERVERPROPERTY('IsPolyBaseInstalled') as IsPolyBaseInstalled,

SERVERPROPERTY('IsSingleUser') as IsSingleUser,

SERVERPROPERTY('IsTempDbMetadataMemoryOptimized') as IsTempDbMetadataMemoryOptimized,

SERVERPROPERTY('IsXTPSupported') as IsXTPSupported,

SERVERPROPERTY('LCID') as LCID,

SERVERPROPERTY('LicenseType') as LicenseType,

SERVERPROPERTY('MachineName') as MachineName,

SERVERPROPERTY('NumLicenses') as NumLicenses,

SERVERPROPERTY('ProcessID') as ProcessID,

SERVERPROPERTY('ProductBuild') as ProductBuild,

SERVERPROPERTY('ProductBuildType') as ProductBuildType,

SERVERPROPERTY('ProductLevel') as ProductLevel,

SERVERPROPERTY('ProductMajorVersion') as ProductMajorVersion,

SERVERPROPERTY('ProductMinorVersion') as ProductMinorVersion,

SERVERPROPERTY('ProductUpdateLevel') as ProductUpdateLevel,

SERVERPROPERTY('ProductUpdateReference') as ProductUpdateReference,

SERVERPROPERTY('ProductVersion') as ProductVersion,

SERVERPROPERTY('ResourceLastUpdateDateTime') as ResourceLastUpdateDateTime,

SERVERPROPERTY('ResourceVersion') as ResourceVersion,

SERVERPROPERTY('ServerName') as ServerName,

SERVERPROPERTY('SqlCharSet') as SqlCharSet,

SERVERPROPERTY('SqlCharSetName') as SqlCharSetName,

SERVERPROPERTY('SqlSortOrder') as SqlSortOrder,

SERVERPROPERTY('SqlSortOrderName') as SqlSortOrderName

;

Performance Dashboard Reports in SQL Server 2008

While running the Performance Dashboard scripts, we will get the following error:

Msg 207, Level 16, State 1, Procedure usp_Main_GetCPUHistory, Line 6
Invalid column name 'cpu_ticks_in_ms'.
Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'usp_Main_GetCPUHistory', because it does not exist or you do not have permission.

To fix it, we need to edit MS_PerfDashboard.usp_Main_GetCPUHistory:

create procedure MS_PerfDashboard.usp_Main_GetCPUHistory

begin

declare @ts_now bigint

--select @ts_now = cpu_ticks / convert(float, cpu_ticks_in_ms) from sys.dm_os_sys_info

select @ts_now = cpu_ticks / convert(float, ms_ticks) from sys.dm_os_sys_info

..................

Delete Duplicate Rows (using CTE)

We can Create a CTE on a table, and add to the table more 'columns', and when CTE is created - DELETE statement can be run on it.

When DELETE command is executed over CTE it in fact deletes from the base table used in CTE.

CREATE TABLE DuplicateRcordTable (Col1 INT, Col2 INT)

INSERT INTO DuplicateRcordTable

VALUES

(1,1),

(1,1), --duplicate

(1,2),

(1,2), --duplicate

(1,3),

(1,4)

SELECT * FROM DuplicateRcordTable

WITH CTE (COl1,Col2, DuplicateCount)

(

SELECT COl1,Col2,

ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount

FROM DuplicateRcordTable

)

DELETE

FROM CTE

WHERE DuplicateCount > 1

SELECT * FROM DuplicateRcordTable

DROP TABLE DuplicateRcordTable

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

-- Another example:

CREATE TABLE DuplicateRcordTable (Col1 INT, Col2 INT, Col3 INT)

INSERT INTO DuplicateRcordTable

VALUES

(1,1,5),

(1,1,4), --duplicate

(1,1,1), --duplicate

(1,2,1),

(1,2,5), --duplicate

(1,3,4),

(1,4,1)

SELECT * FROM DuplicateRcordTable

WITH CTE (COl1,Col2, DuplicateCount)

(

SELECT COl1,Col2,

ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount

FROM DuplicateRcordTable

)

DELETE

FROM CTE

WHERE DuplicateCount > 1

SELECT * FROM DuplicateRcordTable

DROP TABLE DuplicateRcordTable

CLR Functions

You can create a database object inside an instance of SQL Server that is programmed in an assembly created in the .NET Framework common language runtime (CLR).

Database objects that can leverage the rich programming model provided by the common language runtime include aggregate functions, functions, stored procedures, triggers, and types.

1. Create the .Net function

Define the function as a static method of a class in a language supported by the .NET Framework.

2. Compile CLR Code

In order to use this code, the code has to be compiled first to create a DLL.

Run from the CMD:

For vb file:

vbc /target:library C:\Tmp\ClrExample.vb

For C# file:

Csc /target:library C:\Tmp\ClrExample.Cs

3. Enabling CLR Integration:

sp_configure 'clr enabled', 1;

RECONFIGURE;

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

4. Create assembly in SQL Server

CREATE ASSEMBLY [ClrExampleAssembly] FROM 'C:\Tmp\ClrExample.dll'

5. Create a Function or Stored-Procedure using the CLR function

CREATE FUNCTION [dbo].[ClrExampleUserDefinedFunc](<parameters of the function>)

RETURNS <TYPE>

WITH EXECUTE AS CALLER

EXTERNAL NAME ClrExampleAssembly.<Class name in the code>.<Function name in the code>

<TYPE> examples:

TABLE ([StringCol] [nvarchar](max) NULL)

RETURNS [nvarchar](255)

CREATE PROCEDURE dbo.ClrExampleSP

<parameters of the function>

With Execute As Caller

External Name ClrExampleAssembly.<Class name in the code>.<Function name in the code>

6. Use it!

-- FUNCTION:

SELECT * FROM dbo.ClrExampleUserDefinedFunc(<parameters of the function>)

--SP:

Execute ClrExampleSP <parameters of the function>

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

http://msdn.microsoft.com/en-us/library/w2kae45k(v=vs.80).aspx

Passing a datatable to a Stored Procedure in SQL Server 2008

Table-valued parameters are a new parameter type in SQL Server 2008.

Table-valued parameters are declared by using user-defined table types.

You can use table-valued parameters to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.

Of course you can use table-valued parameters in the SQL code.

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

-- create table type

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

CREATE TYPE dbo.TableVariableTest AS TABLE

(

ID int

)

--Note: You can declare a table-valued parameter also with PKs etc.

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

-- create SP that uses the table type

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

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE PROCEDURE dbo.SelectTableVariableTestItai

(

@TableVar dbo.TableVariableTest READONLY

)

BEGIN

SET NOCOUNT ON;

SELECT *

FROM dbo.Calendar c

JOIN @TableVar v ON v.ID = c.CalID

END

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

-- test:

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

DECLARE @MyTable TableVariableTest

INSERT INTO @MyTable(ID)

VALUES (392),

(477),

(506)

EXEC SelectTableVariableTestItai @TableVar = @MyTable

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

-- in the trace it's look like:

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

declare @p1 dbo.TableVariableTest

insert into @p1 values(392)

insert into @p1 values(477)

exec SelectTableVariableTestItai @TableVar=@p1

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

-- the .net code:

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

public DataTable SelectTableVariableTestItai()

{

DataTable dt = new DataTable();

dt.Columns.Add("ID", typeof(int));

DataRow row1 = dt.NewRow();

DataRow row2 = dt.NewRow();

row1["ID"] = 392;

row2["ID"] = 477;

dt.Rows.Add(row1);

dt.Rows.Add(row2);

db.CreateCommand("SelectTableVariableTestItai");

db.AddParameter("TableVar", dt, SqlDbType.Structured);

return db.ExecuteDataTable();

}

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

Enable/Disable Indexes

--Disable Index

ALTER INDEX [IndexName] ON TableName DISABLE

--Enable Index

ALTER INDEX [IndexName] ON TableName REBUILD

SQL Server : Row Versioning-based Isolation Levels - Read_Committed_Snapshot

--An user began a transaction and update few records in a table and won't commit that transaction.

--The user of this session can see the updated uncommited records of this table.

Begin Tran

Update StamTable Set asdsadsad = 999 where asdsadsad is not null

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

--About users from other sessions:

Alter Database ItaiDWSource Set Read_Committed_Snapshot Off

-- Other session won't get result from the table until the lock will be release or transaction will be commit or rollback.

-- The query will be Stuck!

Select * From StamTable

-- also in those queries:

Select * From StamTable With (ReadCommitted) where asdsadsad is not null;

Select * From StamTable where asdsadsad is not null;

-- but, in those queries, we will get the updated uncommited records, and we won't be Stuck.

Select * From StamTable With (NoLock) where asdsadsad is not null;

Select * From StamTable With (ReadUnCommitted) where asdsadsad is not null;

-- NOTE: the risk is, that maybe the transaction will be rollback

Alter Database ItaiDWSource Set Read_Committed_Snapshot on

-- NO query will be Stuck!

-- In those queries we will get the last updated version of the data (before the update query):

Select * From StamTable

Select * From StamTable With (ReadCommitted) where asdsadsad is not null;

Select * From StamTable where asdsadsad is not null;

-- but, in those queries, we will get the updated uncommited records:

Select * From StamTable With (NoLock) where asdsadsad is not null;

Select * From StamTable With (ReadUnCommitted) where asdsadsad is not null;

-- the risk is, that maybe the transaction will be rollback

Read_Committed_Snapshot (RCSI) isolation level:
RCSI save a version to changed data in order to prevent from waits for uncommitted transactions.
RCSI goal is to reduce blocking and deadlocking issues caused by lock contention.

When using RCSI:

Readers DO NOT block Writers.
Writers DO NOT block Readers.
Writers DO block Writers.

Advantages:

SELECT statements do not lock data during a read operation.
As written above, RCSI can reduce blocking and deadlocking issues caused by lock contention.
--> Can improve performance.
Read operations retrieve a consistent snapshot of the database and not uncommitted version (not like in READ UNCOMMITTED that retrieve a not-consistent snapshot).

Disadvantages:

A transaction can be rolled back, and that the version of the data that was received is not true.
Reads involving UDFs under READ_COMMITTED_SNAPSHOT may seem inconsistent, read about it here: http://sqlblog.com/blogs/alexander_kuznetsov/archive/2011/08/02/reads-involving-udfs-under-read-committed-snapshot-may-seem-inconsistent.aspx
The versioned rows for RCSI are kept in tempdb, so there is affect on tempdb performance when using RCSI.

Read committed	Read uncommitted	RCSI
Yes	No	No	Locks while SELECT
No	Yes	No	Corrupted Data
Yes	No	No	The most updated data
Wait for commit\rollback	Get the current (and maybe the dirty) data in the table, before commit\rollback.	Get the last updated version of the data (before the first transaction)	In case of change in transaction, and in another transaction there is a Select on the updated data
Yes	Yes	Yes	Locks while UPDATE

-- Get the Read_Committed_Snapshot status:

Select name, is_read_committed_snapshot_on

From sys.databases Where database_id = DB_ID()

-- On = 1, Off = 0

SQL Server String length

SELECT LEN (string_expression)

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

Disable/Enable a Constraint on a table

ALTER TABLE TableName <CHECK / NOCHECK> CONSTRAINT ConstraintName

Examples:

--disable constraint

ALTER TABLE dbo.StamTable2 NOCHECK CONSTRAINT StamTable2_check

--enable constraint

ALTER TABLE dbo.StamTable2 CHECK CONSTRAINT StamTable2_check

--disable all constraints for the dbo.StamTable2 table

ALTER TABLE dbo.StamTable2 NOCHECK CONSTRAINT ALL

--enable all constraints for the dbo.StamTable2 table

ALTER TABLE dbo.StamTable2 CHECK CONSTRAINT ALL

NOTE: This action applies only to foreign key and check constraints.

Labels