SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Deadlocks


SQL Deadlocks

Author
Message
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88266 Visits: 45277
bugg (1/22/2013)
GilaMonster (1/22/2013)
bugg (1/22/2013)
GilaMonster (1/22/2013)
bugg (1/22/2013)
Any insight into dealing with deadlocks would be greatly appreciated.


In general:
Optimise the queries. If that doesn't fix the problem use one of the row versioning-based isolation levels



Thanks Gila, I'm going to try using Rowlock with the update commands and see if that clears the locks.


Optimise your queries, don't mess with locking hints unless you really understand what you're doing and why and have optimised as much as possible.
You do realise that a row lock hint means more chance of lock escalation (to table), not less?


Nope I did not realize that! I thought it was specific to the row being updated Unsure


Depends how many rows the update effects. If it's one row (and the stats estimate that), SQL would probably go for a row lock anyway. If it's several thousand rows, SQL would probably go for page locks, if you put a row lock hint on, SQL will start with row locks, if the number of locks held pass a threshold, it escalates to table locks.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


bugg
bugg
SSChasing Mays
SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)

Group: General Forum Members
Points: 644 Visits: 984
anthony.green (1/22/2013)
Do you have traceflag 1222 enabled?

If so can you post the deadlock graph from the error log

If not run

DBCC TRACEON (1222, -1)

and then post the graphs once a deadlock has occured.


Hi Anthony

I have this trace on but cant find the deadlock graph anywhere in the errorlog? The deadlock has been recorded but thats all.
anthony.green
anthony.green
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10152 Visits: 6375
Do you have the deadlock XML in the error log, should be many many lines that looks like this


<TextData><deadlock-list>
<deadlock victim="process53b9288">
<process-list>
<process id="process53b9288" taskpriority="0" logused="476" waitresource="KEY: 6:72057594136035328 (ac00e9dcc382)" waittime="3386" ownerId="26173739408" transactionname="user_transaction" lasttranstarted="2013-01-22T22:47:20.203" XDES="0x11a64d970" lockMode="S" schedulerid="8" kpid="15240" status="suspended" spid="119" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2013-01-22T22:47:20.207" lastbatchcompleted="2013-01-22T22:47:20.207" clientapp="EntityFramework" hostname="App1" hostpid="8824" loginname="Ant" isolationlevel="read committed (2)" xactid="26173739408" currentdb="1" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">





Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


bugg
bugg
SSChasing Mays
SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)

Group: General Forum Members
Points: 644 Visits: 984
anthony.green (1/23/2013)
Do you have the deadlock XML in the error log, should be many many lines that looks like this


<TextData><deadlock-list>
<deadlock victim="process53b9288">
<process-list>
<process id="process53b9288" taskpriority="0" logused="476" waitresource="KEY: 6:72057594136035328 (ac00e9dcc382)" waittime="3386" ownerId="26173739408" transactionname="user_transaction" lasttranstarted="2013-01-22T22:47:20.203" XDES="0x11a64d970" lockMode="S" schedulerid="8" kpid="15240" status="suspended" spid="119" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2013-01-22T22:47:20.207" lastbatchcompleted="2013-01-22T22:47:20.207" clientapp="EntityFramework" hostname="App1" hostpid="8824" loginname="Ant" isolationlevel="read committed (2)" xactid="26173739408" currentdb="1" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">




Hi Anthony, I don't have any deadlock XML in my error log but i do have the information above just broken up into lines.

01/23/2013 07:25:31,spid23s,Unknown,waiter id=process27317b4c8 mode=X requestType=wait
01/23/2013 07:25:31,spid23s,Unknown,waiter-list
01/23/2013 07:25:31,spid23s,Unknown,owner id=processdb54c8 mode=S
01/23/2013 07:25:31,spid23s,Unknown,owner-list
01/23/2013 07:25:31,spid23s,Unknown,keylock hobtid=72057605790367744 dbid=4 objectname=dbo.order indexname=IX_status id=lock3d60c8e80 mode=S associatedObjectId=72057605790367744
01/23/2013 07:25:31,spid23s,Unknown,waiter id=processdb54c8 mode=S requestType=wait
01/23/2013 07:25:31,spid23s,Unknown,waiter-list
01/23/2013 07:25:31,spid23s,Unknown,owner id=process27317b4c8 mode=X
01/23/2013 07:25:31,spid23s,Unknown,owner-list
01/23/2013 07:25:31,spid23s,Unknown,keylock hobtid=72057602492792832 dbid=4 objectname=dbo.order indexname=PK_order id=lock3d27aec80 mode=X associatedObjectId=72057602492792832
01/23/2013 07:25:31,spid23s,Unknown,resource-list
01/23/2013 07:25:31,spid23s,Unknown,update order set shipped=1<c/>status=3<c/>shipdate=getdate() where sessionid='125693' and order=1
01/23/2013 07:25:31,spid23s,Unknown,inputbuf
01/23/2013 07:25:31,spid23s,Unknown,update order set shipped=1<c/>status=3<c/>shipdate=getdate() where sessionid='125693' and order=1
01/23/2013 07:25:31,spid23s,Unknown,frame procname=adhoc line=1 sqlhandle=0x02000000880e2612bdc1c85889b28ed1a83337436c23418e
01/23/2013 07:25:31,spid23s,Unknown,(@1 int<c/>@2 int<c/>@3 varchar(8000)<c/>@4 tinyint)UPDATE [order] set [shipped] = @1<c/>[status] = @2<c/>[shipdate] = getdate() WHERE [sessionid]%%=%%@3 AND [order]%%=%%@4
01/23/2013 07:25:31,spid23s,Unknown,frame procname=adhoc line=1 sqlhandle=0x020000006d90e92510cd4c7f9a9af4c3c7d68bb4cd95914a
01/23/2013 07:25:31,spid23s,Unknown,executionStack
01/23/2013 07:25:31,spid23s,Unknown,process id=process27317b4c8 taskpriority=0 logused=292 waitresource=KEY: 9:72057605790367744 (2e001d1b37ce) waittime=1840 ownerId=18323630766 transactionname=UPDATE lasttranstarted=2013-01-23T07:25:29.427 XDES=0xe4e2b970 lockMode=X schedulerid=8 kpid=2092 status=suspended spid=59 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2013-01-23T07:25:29.420 lastbatchcompleted=2013-01-23T07:25:29.420 clientapp=PHP 5 hostpid=227446 isolationlevel=read committed (2) xactid=18323630766 currentdb=4 lockTimeout=4294967295
01/23/2013 07:25:31,spid23s,Unknown,inputbuf
01/23/2013 07:25:31,spid23s,Unknown,SELECT count(rsn) As Count FROM order WHERE status<3 AND shipopt <99 AND dateadded <= '2013-01-23 13:00:59'
01/23/2013 07:25:31,spid23s,Unknown,frame procname=adhoc line=1 sqlhandle=0x02000000712b952eb475b9b249be45c1513acca8b2b31ed1
01/23/2013 07:25:31,spid23s,Unknown,(@1 tinyint<c/>@2 tinyint<c/>@3 tinyint<c/>@4 varchar(8000))SELECT COUNT([rsn]) [Count] FROM [order] WHERE [status]<@1 AND [shipopt]<@3 AND [dateadded]<=@4
01/23/2013 07:25:31,spid23s,Unknown,frame procname=adhoc line=1 sqlhandle=0x020000007c853201340a85cf70825b9c171de622351e36b1
01/23/2013 07:25:31,spid23s,Unknown,executionStack
01/23/2013 07:25:31,spid23s,Unknown,process id=processdb54c8 taskpriority=0 logused=0 waitresource=KEY: 9:72057602492792832 (2c00849cfb3c) waittime=1840 ownerId=18323630729 transactionname=SELECT lasttranstarted=2013-01-23T07:25:29.423 XDES=0x1c384fb80 lockMode=S schedulerid=7 kpid=8128 status=suspended spid=51 sbid=0 ecid=4 priority=0 trancount=0 lastbatchstarted=2013-01-23T07:25:29.423 lastbatchcompleted=2013-01-23T07:25:29.280 clientapp=PHP 5 hostpid=227446 isolationlevel=read committed (2) xactid=18323630729 currentdb=4 lockTimeout=4294967295
01/23/2013 07:25:31,spid23s,Unknown,process-list
01/23/2013 07:25:31,spid23s,Unknown,deadlock victim=processdb54c8
01/23/2013 07:25:31,spid23s,Unknown,deadlock-list
01/23/2013 07:25:31,spid5s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x00000001C384FB80 Mode: S SPID:51 BatchID:0 ECID:4 TaskProxySad0x000000035B1E3770) Value:0xce952bc0 CostSad0/0)
01/23/2013 07:25:31,spid5s,Unknown,Victim Resource Owner:
01/23/2013 07:25:31,spid5s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
01/23/2013 07:25:31,spid5s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x00000000E4E2B970 Mode: X SPID:59 BatchID:0 ECID:0 TaskProxySad0x000000024AD44538) Value:0xbd6264c0 CostSad0/292)
01/23/2013 07:25:31,spid5s,Unknown,Requested by:
01/23/2013 07:25:31,spid5s,Unknown,Input Buf: No Event:
01/23/2013 07:25:31,spid5s,Unknown,SPID: 51 ECID: 4 Statement Type: SELECT Line #: 1
01/23/2013 07:25:31,spid5s,Unknown,Owner:0x00000001BF286480 Mode: S Flg:0x40 Ref:0 Life:00000001 SPID:51 ECID:4 XactLockInfo: 0x00000001C384FBC0
01/23/2013 07:25:31,spid5s,Unknown,Grant List 2:
01/23/2013 07:25:31,spid5s,Unknown,KEY: 9:72057605790367744 (2e001d1b37ce) CleanCnt:2 Mode:S Flags: 0x1
01/23/2013 07:25:31,spid5s,Unknown,Node:2
01/23/2013 07:25:31,spid5s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
01/23/2013 07:25:31,spid5s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x00000001C384FB80 Mode: S SPID:51 BatchID:0 ECID:4 TaskProxySad0x000000035B1E3770) Value:0xce952bc0 CostSad0/0)
01/23/2013 07:25:31,spid5s,Unknown,Requested by:
01/23/2013 07:25:31,spid5s,Unknown,Input Buf: Language Event: update order set shipped=1<c/>status=3<c/>shipdate=getdate() where sessionid='125693' and order=1
01/23/2013 07:25:31,spid5s,Unknown,SPID: 59 ECID: 0 Statement Type: UPDATE Line #: 1
01/23/2013 07:25:31,spid5s,Unknown,Owner:0x000000008164B2C0 Mode: X Flg:0x40 Ref:1 Life:02000000 SPID:59 ECID:0 XactLockInfo: 0x00000000E4E2B9B0
01/23/2013 07:25:31,spid5s,Unknown,Grant List 1:
01/23/2013 07:25:31,spid5s,Unknown,KEY: 9:72057602492792832 (2c00849cfb3c) CleanCnt:2 Mode:X Flags: 0x1
01/23/2013 07:25:31,spid5s,Unknown,Node:1
01/23/2013 07:25:31,spid5s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
01/23/2013 07:25:31,spid5s,Unknown,Wait-for graph
01/23/2013 07:25:31,spid5s,Unknown,Deadlock encountered .... Printing deadlock information

GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88266 Visits: 45277
bugg (1/23/2013)
Hi Anthony, I don't have any deadlock XML in my error log but i do have the information above just broken up into lines.


That's a deadlock graph. 2 actually, since you enabled both traceflags 1204 and 1222 resulting an a messed up combination of old style of old and new deadlock graphs

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


bugg
bugg
SSChasing Mays
SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)

Group: General Forum Members
Points: 644 Visits: 984
GilaMonster (1/23/2013)
bugg (1/23/2013)
Hi Anthony, I don't have any deadlock XML in my error log but i do have the information above just broken up into lines.


That's a deadlock graph. 2 actually, since you enabled both traceflags 1204 and 1222 resulting an a messed up combination of old style of old and new deadlock graphs


Ohh...I was expecting an actual graph. I will disable the old trace. Still not sure how to eliminate the deadlock.
anthony.green
anthony.green
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10152 Visits: 6375
All statistics and indexes rebuilt and updated on the order table?

Can you post the execution plans as a SQLPLAN file from the below query, if they exist any more.


select
query_plan
from
sys.dm_exec_query_stats
cross apply
sys.dm_exec_query_plan (plan_handle)
where
sql_handle in
(
0x02000000880e2612bdc1c85889b28ed1a83337436c23418e,
0x020000006d90e92510cd4c7f9a9af4c3c7d68bb4cd95914a,
0x020000007c853201340a85cf70825b9c171de622351e36b1
)





Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88266 Visits: 45277
Can you post the table definition and all index definitions for dbo.order?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


bugg
bugg
SSChasing Mays
SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)

Group: General Forum Members
Points: 644 Visits: 984
anthony.green (1/23/2013)
All statistics and indexes rebuilt and updated on the order table?

Can you post the execution plans as a SQLPLAN file from the below query, if they exist any more.


select
query_plan
from
sys.dm_exec_query_stats
cross apply
sys.dm_exec_query_plan (plan_handle)
where
sql_handle in
(
0x02000000880e2612bdc1c85889b28ed1a83337436c23418e,
0x020000006d90e92510cd4c7f9a9af4c3c7d68bb4cd95914a,
0x020000007c853201340a85cf70825b9c171de622351e36b1
)



The Indexes are rebuilt every night depending on how fragmented they are. They all seem to be pretty much up to date on the order table.
Unfortunately the execution plans do not exist anymore.
bugg
bugg
SSChasing Mays
SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)

Group: General Forum Members
Points: 644 Visits: 984
GilaMonster (1/23/2013)
Can you post the table definition and all index definitions for dbo.order?


Just spotted there are 2 indexes on session_id I will drop one of them.


CREATE TABLE [dbo].[order](
[rsn] [bigint] IDENTITY(1,1) NOT NULL,
[sessionid] [nvarchar](50) NOT NULL,
[order] [int] NOT NULL,
[perc] [decimal](18, 8) NOT NULL,
[userid] [bigint] NOT NULL,
[hashtotal] [decimal](18, 2) NOT NULL,
[dateadded] [datetime] NOT NULL,
[shipped] [tinyint] NOT NULL,
[status] [int] NOT NULL,
[suspectorder] [tinyint] NOT NULL,
[shippingweight] [decimal](18, 2) NOT NULL,
[discount] [decimal](18, 2) NOT NULL,
[shiptotal] [decimal](18, 2) NOT NULL,
[shipopt] [int] NOT NULL,
[rd] [tinyint] NOT NULL,
[giftwrap] [tinyint] NOT NULL,
[bay] [int] NOT NULL,
[instock] [tinyint] NOT NULL,
[beenupdated] [tinyint] NOT NULL,
[shipdate] [datetime] NULL,
[vatamount] [decimal](18, 2) NOT NULL,
[prodname] [nvarchar](250) NOT NULL,
[source] [char](2) NOT NULL,
[externalsessionid] [nvarchar](70) NOT NULL,
[postalrsn] [bigint] NOT NULL,
[printref] [nvarchar](50) NOT NULL,
[sdref] [nvarchar](20) NOT NULL,
[oos] [tinyint] NOT NULL,
[sdreflabel] [nvarchar](20) NOT NULL,
[bin] [nvarchar](20) NOT NULL,
CONSTRAINT [PK_order] PRIMARY KEY CLUSTERED
(
[rsn] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

/****** Object: Index [IX_dateadded] Script Date: 01/23/2013 15:29:30 ******/
CREATE NONCLUSTERED INDEX [IX_dateadded] ON [dbo].[order]
(
[dateadded] ASC
)
INCLUDE ( [shiptotal]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

/****** Object: Index [IX_gw] Script Date: 01/23/2013 15:29:30 ******/
CREATE NONCLUSTERED INDEX [IX_gw] ON [dbo].[order]
(
[giftwrap] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

/****** Object: Index [IX_oh] Script Date: 01/23/2013 15:29:30 ******/
CREATE NONCLUSTERED INDEX [IX_oh] ON [dbo].[order]
(
[order] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

/****** Object: Index [IX_printeref] Script Date: 01/23/2013 15:29:30 ******/
CREATE NONCLUSTERED INDEX [IX_printeref] ON [dbo].[order]
(
[printref] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

/****** Object: Index [IX_rd] Script Date: 01/23/2013 15:29:30 ******/
CREATE NONCLUSTERED INDEX [IX_rd] ON [dbo].[order]
(
[rd] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

/****** Object: Index [IX_sess] Script Date: 01/23/2013 15:29:30 ******/
CREATE NONCLUSTERED INDEX [IX_sess] ON [dbo].[order]
(
[sessionid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

/****** Object: Index [IX_sessionid] Script Date: 01/23/2013 15:29:30 ******/
CREATE NONCLUSTERED INDEX [IX_sessionid] ON [dbo].[order]
(
[sessionid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


/****** Object: Index [IX_shipopt] Script Date: 01/23/2013 15:29:30 ******/
CREATE NONCLUSTERED INDEX [IX_shipopt] ON [dbo].[order]
(
[shipopt] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO


/****** Object: Index [IX_status] Script Date: 01/23/2013 15:29:30 ******/
CREATE NONCLUSTERED INDEX [IX_status] ON [dbo].[order]
(
[status] ASC
)
INCLUDE ( [printref]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

/****** Object: Index [IX_suspect] Script Date: 01/23/2013 15:29:30 ******/
CREATE NONCLUSTERED INDEX [IX_suspect] ON [dbo].[order]
(
[suspectorder] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO


ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_sessionid] DEFAULT ('') FOR [sessionid]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_order] DEFAULT ((0)) FOR [order]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_perc] DEFAULT ((0)) FOR [perc]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_userid] DEFAULT ((0)) FOR [userid]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_hashtotal] DEFAULT ((0)) FOR [hashtotal]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_dateadded] DEFAULT (getdate()) FOR [dateadded]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_shipped] DEFAULT ((0)) FOR [shipped]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_shipping] DEFAULT ((0)) FOR [status]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_suspectorder] DEFAULT ((0)) FOR [suspectorder]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_shippingweight] DEFAULT ((0)) FOR [shippingweight]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_discount] DEFAULT ((0)) FOR [discount]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_shiptotal] DEFAULT ((0)) FOR [shiptotal]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_shipopt] DEFAULT ((0)) FOR [shipopt]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_rd] DEFAULT ((0)) FOR [rd]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_giftwrap] DEFAULT ((0)) FOR [giftwrap]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_bay] DEFAULT ((0)) FOR [bay]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_instock] DEFAULT ((0)) FOR [instock]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_beenupdated] DEFAULT ((0)) FOR [beenupdated]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_vatamount] DEFAULT ((0)) FOR [vatamount]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_prodname] DEFAULT ('') FOR [prodname]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_source] DEFAULT ('IC') FOR [source]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_externalsessionid] DEFAULT ('') FOR [externalsessionid]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_postalrsn] DEFAULT ((0)) FOR [postalrsn]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_printref] DEFAULT ('') FOR [printref]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_sdref] DEFAULT ('') FOR [sdref]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_oos] DEFAULT ((0)) FOR [oos]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_sdreflabel] DEFAULT ('') FOR [sdreflabel]
GO

ALTER TABLE [dbo].[order] ADD CONSTRAINT [DF_order_bin] DEFAULT ('') FOR [bin]
GO



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search