Check constraint on a column value for each ID in the table

Our goal: to check in the DB level that for each ID (or other column) will be other column with a specific value not more than one time.

Wow - long statement… Let's do it with an example :)

CREATE TABLE [dbo].[SpecialCheckConst]

(      SomeID int,

       SomeStatusID int, -- we will check vaalues of this column

       SomeOtherColumn nvarchar(50) -- or any other columns

)

We want to check that for each SomeID there is no more than one record of SomeStatusID 1.

INSERT INTO dbo.SpecialCheckConst

       (      SomeID, SomeStatusID, SomeOtherColumn)

       VALUES

       (      2, 3, 'aaa'), (5, 1, 'sss')

When we will try to insert new record with SomeStatusID 1 for SomeID 2 - we should success.

When we will try to insert new record with SomeStatusID 1 for SomeID 5 - we should fail (because there is a record for this SomeID with status 1).

When we will try to insert new record with new SomeID - of course that SomeStatusID 1 is OK.

Solution: 

CHECK CONSTRAINT that calls a function that count the number of SomeStatusID 1 for the SomeID:

CREATE FUNCTION [dbo].[fn_SpecialCheckConst_SomeStatusIDOne]

(

       @SomeID int

)

RETURNS INT

AS

BEGIN

       DECLARE @SomeStatusIDOne int

       SELECT @SomeStatusIDOne = COUNT(*)

       FROM dbo.SpecialCheckConst

       WHERE SomeID = @SomeID

       AND SomeStatusID = 1

       RETURN @SomeStatusIDOne

END

GO

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

ALTER TABLE [dbo].[SpecialCheckConst]  WITH CHECK ADD  CONSTRAINT [CHK_dbo_SpecialCheckConst_SomeStatusIDOne]

CHECK  (NOT ([dbo].[fn_SpecialCheckConst_SomeStatusIDOne](@SomeID) > 1 AND SomeStatusID = 1))

GO

ALTER TABLE [dbo].[SpecialCheckConst] CHECK CONSTRAINT [CHK_dbo_SpecialCheckConst_SomeStatusIDOne]

GO

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

And ….. It’s work!

You can drop the example temp table:

--DROP TABLE dbo.SpecialCheckConst

--go

update Unicode strings in database

In SQL, in order to update Unicode values, you should add ‘N’ before the string

update [dbo].[Player]

set address1=N'aąbcćdeęfghijklłmnńoóprsśtuwyzźż',

address2=N'AĄBCĆDEĘFGHIJKLŁMNŃOÓPRSŚTUWYZŹŻ'

where alias=N'Auto77086780' or Address1=N'tägelîch'

Here an example how an update statement without this ‘N’ will update wrong value (first update),

and how the same update statement but with ‘N’ will update the correct value (second update).

(BTW - the text is in Polish.)

datediff on LAG expression error - The datediff function resulted in an overflow

Query:

; with t as

(      SELECT [ID],

                     [LastRunTime],

                     LAG([LastRunTime], 1,0) OVER (ORDER BY [LastRunTime])  AS LAGLastRunTime

              , .....

       FROM .....

)

SELECT *, DATEDIFF(second, LAGLastRunTime , isnull(LastRunTime, '20010101')) as delta_

FROM t

Error message:

Msg 535, Level 16, State 0, Line 1

The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

Reason for the error:

The “guilty” is the first record – that don’t have LAG date – so the LAG value is ‘1900-01-01 00:00:00.000’.

And then –datediff can’t go with so big int value  - the seconds/minutes/etc. between 1900 to the first date – that is probably more than 100 years… J

Solution:

Convert the first date to some reasonable date:

; with t as

(      SELECT [ID],

                     [LastRunTime],

                     ( case

                           when LAG([LastRunTime], 1,0) OVER (ORDER BY [LastRunTime]) > '20010101'

                                  then LAG([LastRunTime], 1,0) OVER (ORDER BY [LastRunTime])

                                  else [LastRunTime] end

                     )  AS LAGLastRunTime

              , .....

       FROM .....

)

SELECT *, DATEDIFF(second, LAGLastRunTime , isnull(LastRunTime, '20010101')) as delta_

FROM t

Get the list of the database and log files contained in the backup file

restore filelistonly from disk = 'C:\temp\MyBackUpFile.bak'

CPU usage per database

WITH DB_CPU_Statistics AS

(      SELECT pa.DatabaseID, DB_NAME(pa.DatabaseID) AS [Database Name],

                     SUM(qs.total_worker_time/1000) AS [CPU_Time_Ms]

       FROM          sys.dm_exec_query_stats AS qs WITH (NOLOCK)

       CROSS APPLY   (      SELECT CONVERT(INT, value) AS [DatabaseID]

                                  FROM sys.dm_exec_plan_attributes(qs.plan_handle)

                                  WHERE attribute = N'dbid'

                           ) AS pa

       GROUP BY DatabaseID

)

SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [CPU Ranking],

              [Database Name], [CPU_Time_Ms] AS [CPU Time (ms)],

              CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPU Percent]

FROM DB_CPU_Statistics

WHERE DatabaseID <> 32767 -- ResourceDB

ORDER BY [CPU Ranking] OPTION (RECOMPILE);

SELECT the first of current month

SELECT DATEADD(month, DATEDIFF(month, 0, getdate()), 0) AS FirstOfCurrentMonth

Error when create new column and run some things on it

if not exists (select * from sys.columns where object_id = object_id('dbo.MyTable') and name = 'NewColumn')

begin

                alter table Customer.Configuration add NewColumn int null

end

do something with the new column - update, alter, etc.

--> Error message:

Msg 207, Level 16, State 1, Server <MyServer>, Line 17 Invalid column name 'NewColumn'.

Solution:

Close the batch pf the creation by adding 'GO' between the column creatuion and usage.

 if not exists (select * from sys.columns where object_id = object_id('dbo.MyTable') and name = 'NewColumn')

begin

                alter table Customer.Configuration add NewColumn int null

end

GO

do something with the new column - update, alter, etc.

Check the SQL server port number

In order to check the SQL server port number,

open:

Start button (windows) --> Go to All Programs --> Microsoft SQL Server 20XX --> Configuration Tools --> SQL Server Configuration Manager

And there: 

SQL Native Client 10.0 Configuration --> Client Protocols --> TCP/IP Properties (double click or Right click)

There you will see the default port number.

Ctrl+R doesn't work in SQL Server 2012 SSMS

Problem: Ctrl+R doesn't work in SQL Server 2012 SSMS!!!

When I started work with SSMS of SQL Server 32012, and tried to hide/display the result panel, using Ctrl+R – it doesn't work for me, and I’v got this error in the bottom of the SSMS:

(Ctrl+R) was pressed. Waiting for second key of chord.

Solution:

Open from the menu: Toolsàoptions, choose KeyboardàKeyboard.

There, choose from the list: “window.ShowResultPane” event, and assign the Ctrl+R to “Use new shortcut in: SQL Query Editor”.

(Don’t forget to press the “Assign” button…)

Script fpr Role creation including permissions

-- set here the role name:

declare @RoleName varchar(50) = 'ROLE_NAME'

declare @RoleScript varchar(max)

SELECT @RoleScript = 'CREATE ROLE ' + @RoleName + char(13)

SELECT @RoleScript = @RoleScript + 'GRANT ' + prm.permission_name + ' ON ' + OBJECT_NAME(major_id) + ' TO ' + rol.name + char(13) COLLATE Latin1_General_CI_AS

FROM   sys.database_permissions prm

JOIN      sys.database_principals rol ON prm.grantee_principal_id = rol.principal_id

WHERE  rol.name = @RoleName

PRINT @RoleScript

Transaction was deadlocked on lock resources with another process and has been chosen as the deadlock victim

Error message:

Transaction (Process ID 64) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

But if I don’t want this process to be the deadlock victim?

Solution:

SET DEADLOCK_PRIORITY HIGH

see here:

http://copypastenet.blogspot.co.il/2014/01/set-deadlockpriority.html

Drop few columns in one statement

ALTER TABLE dbo.MyTable DROP COLUMN ColumnA, ColumnB, ColumnC

Pivot On Multiple Columns

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

-- temp table for the example

CREATE TABLE tmpResults

( CustomerType nvarchar(50),

SomeTypeLetter nvarchar(50),

NumOfCustomers int

)

INSERT INTO tmpResults (CustomerType, SomeTypeLetter, NumOfCustomers)

VALUES ('CustomerType1', 'SomeTypeA', 33),

('CustomerType1', 'SomeTypeB', 12),

('CustomerType1', 'SomeTypeC', 61),

('CustomerType2', 'SomeTypeA', 11),

('CustomerType2', 'SomeTypeB', 25),

('CustomerType2', 'SomeTypeC', 21),

('CustomerType3', 'SomeTypeA', 17),

('CustomerType3', 'SomeTypeB', 38),

('CustomerType3', 'SomeTypeC', 9),

('CustomerType4', 'SomeTypeA', 5),

('CustomerType4', 'SomeTypeB', 91),

('CustomerType4', 'SomeTypeC', 12)

--SELECT * FROM tmpResults

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

--Pivot On Multiple Columns:

SELECT [CustomerType1-SomeTypeA], [CustomerType1-SomeTypeB], [CustomerType1-SomeTypeC],

[CustomerType2-SomeTypeA], [CustomerType2-SomeTypeB], [CustomerType2-SomeTypeC],

[CustomerType3-SomeTypeA], [CustomerType3-SomeTypeB], [CustomerType3-SomeTypeC],

[CustomerType4-SomeTypeA], [CustomerType4-SomeTypeB], [CustomerType4-SomeTypeC]

FROM 

(   SELECT  isnull(p.NumOfCustomers,0) as NumOfCustomers,

(p.CustomerType) + '-' + (p.SomeTypeLetter) as [desc]

FROM    tmpResults p

) [base]

PIVOT

(   SUM(NumOfCustomers)

FOR [desc] IN

(

[CustomerType1-SomeTypeA], [CustomerType1-SomeTypeB], [CustomerType1-SomeTypeC],

[CustomerType2-SomeTypeA], [CustomerType2-SomeTypeB], [CustomerType2-SomeTypeC],

[CustomerType3-SomeTypeA], [CustomerType3-SomeTypeB], [CustomerType3-SomeTypeC],

[CustomerType4-SomeTypeA], [CustomerType4-SomeTypeB], [CustomerType4-SomeTypeC]

)

) [pvt]

DROP TABLE tmpResults

T-SQL that return data of the SQL Server databases

Select @@SERVERNAME -- Server and instance name

Select @@VERSION -- SQL Server Version

Select @@ServiceName -- SQL Server Instance

Select DB_NAME() -- Current Database

SELECT * FROM sys.servers ORDER BY name --Linked Servers

--Last Databases Backup:

SELECT  @@Servername AS ServerName ,

        d.Name AS DBName ,

        MAX(b.backup_finish_date) AS LastBackupCompleted

FROM    sys.databases d

LEFT OUTER JOIN msdb..backupset b 

        ON b.database_name = d.name AND b.[type] = 'D'

GROUP BY d.Name

ORDER BY d.Name;

And more - in the article "Exploring Your SQL Server Databases with T-SQL":

https://www.simple-talk.com/sql/database-administration/exploring-your-sql-server-databases-with-t-sql/?utm_source=ssc&utm_medium=publink&utm_content=exploringwithtsql

Partition info

select distinct

object_name(p.object_id),

      p.partition_number,

      pf.name,

      ps.name,

      prv.value,

      p.data_compression_desc,

      fg.name,

      p.rows

from  sys.partitions (nolock) p

inner join sys.indexes (nolock)i on p.object_id = i.object_id and p.index_id = i.index_id

inner join sys.partition_schemes ps (nolock) on ps.data_space_id=i.data_space_id

inner join sys.partition_functions (nolock)pf on pf.function_id=ps.function_id

inner join sys.partition_range_values(nolock) prv on p.partition_number = prv.boundary_id and prv.function_id=pf.function_id

inner join sys.allocation_units au  ON au.container_id = p.hobt_id

inner join sys.filegroups fg  ON fg.data_space_id = au.data_space_id

where pf.name='PF_Name'

and object_name(p.object_id) = 'TableName'

--and partition_number<386

--and cast(prv.value as datetime) > '2014-03-04 00:00:00.000'

order by object_name(p.object_id),p.partition_number-- desc

Current running query

select getdate() date_time, t3.session_id, host_name, t3.status,DB_NAME(t2.database_id) DataBase_Name, 

blocking_session_id AS Blocked_by,SUBSTRING(t1.text, (t2.statement_start_offset/2)+1,

((CASE t2.statement_end_offset WHEN -1 THEN DATALENGTH(t1.text) WHEN 0 THEN DATALENGTH(t1.text) ELSE t2.statement_end_offset END - t2.statement_start_offset)/2) + 1) AS command,

t3.cpu_time, t3.reads, t3.writes, wait_time, t2.wait_type, last_request_end_time,program_name, t1.objectid 

from sys.dm_exec_sessions t3 

left join sys.dm_exec_requests t2 on t3.session_id=t2.session_id 

outer APPLY sys.dm_exec_sql_text (t2.sql_handle) t1 

where t3.status not in ('sleeping','dormant') and program_name not like 'sqlagent%'

Incorrect syntax near the keyword 'SCHEMA'

If you try to execute:

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'NewSchemaName')

       CREATE SCHEMA [NewSchemaName] AUTHORIZATION [dbo]

GO

You will get this error message:

Msg 156, Level 15, State 1, Line 2

Incorrect syntax near the keyword 'SCHEMA'.

And it although this command will success:

CREATE SCHEMA [NewSchemaName] AUTHORIZATION [dbo]

So why we’ve got the error?

Because schema creation must be the first command in a batch.

A solution:
Run it as dynamic SQL:

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'NewSchemaName')

       EXEC sp_executesql N'CREATE SCHEMA [NewSchemaName] AUTHORIZATION [dbo]'

GO

Tempdb contention on sysmultiobjrefs (2:1:103) causes performance problems

Tempdb contention on sysmultiobjrefs (2:1:103) causes performance problems

The locks in tempdb were from type page latch on file 1 page 103 (2:1:103).

We can see it in our locks table.

Unfortunately this exact page is knows is problematic and also no complete solution currently exist.

Here are a few interesting links

Paul Randal, one of the leading SQL persons says that there is no solution:

That “the SQL Team knows about this. It’s a known issue. Hopefully something will be done about it in one of the future releases.”

But unlike SGAM or GAM contention, there’s absolutely nothing you can do about this to spread the contention around.

http://www.sqlskills.com/insidercontent/201106/20110612newsletter.pdf  

Microsoft are aware of it:

As you mentioned, this is a known issue. We did do some updates in this area that may address some (but unfortunately not all) of these scenarios. These should be in the latest release of SQL Server 2008 R2 and SQL Server 2012. If possible, can you please try out the latest updates and see if they help? Again, this won't fix every scenario. I am going to close this Connect item as fixed but if you are still seeing issues with specific scenarios, can you please open new Connect issues to track those?

https://connect.microsoft.com/SQLServer/feedback/details/777072/

Though there are ways to try and minimize this problem:

We know that we have trouble when there are queries that:

• are executed frequently
• create temp tables (either explicitly or by declaring table valued parameters).
• are not cached (Microsoft explains when temp tables are not cached. In my case, it was because they were ad-hoc queries)
• require a page latch on 2:1:103.

http://michaeljswart.com/2013/01/pagelatch-contention-on-21103/

Start job from a specific step (not only the first step)

Start job from a specific step (not only the first step) with this command:

EXEC sp_start_job @job_name = 'JobName', @step_name = 'JobStepName'

GO