Viewing 15 posts - 5,251 through 5,265 (of 7,619 total)
Matthew Darwin (4/1/2015)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 1, 2015 at 2:30 pm
You can try to get SQL to make the ALTER the task that SQL kills if a deadlock occurs, but I've found it's not 100% effective.
SET DEADLOCK_PRIORITY -8
ALTER INDEX ALL...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 1, 2015 at 1:30 pm
Cluster the original table on ( CreatedDate, SmsId ), in that order, not vice-versa.
Once you get the best clustering key, you're likely not to need partitioning yet. And, if...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 1, 2015 at 1:27 pm
I've seen it cause errors, although it's somewhat obscure, a combination of things, including linked servers, IIRC. Perhaps it's been fixed, but I don't intend to risk job failures...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 1, 2015 at 8:55 am
CELKO (3/31/2015)
use lowercase only for data type names in SQL Server
.
No. The human eye reads certain words as a single unit called a bouma. The keywords in programming...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 31, 2015 at 4:02 pm
A JOIN is preferred over the older-style WHERE clause, although with an INNER JOIN they should function the same either way:
INSERT INTO myTable
(ID,someData,someData2)
SELECT
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 31, 2015 at 12:32 pm
It's some version of DB2 / UDB.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 31, 2015 at 11:02 am
As a side note, but still critical, let me warn everyone to use lowercase only for data type names in SQL Server. That is, "int" not "INT", "datetime" not...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 31, 2015 at 10:58 am
Using an in-line tally table would be more efficient:
/* add in-line tally table (CROSS JOINs of 10 x 10 = 100; 100 x 100 = 10K) here,
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 31, 2015 at 10:10 am
George Wei (3/28/2015)
Our system runs a SQL Server 2012 DB, it has a table (table_a) which has over 10M records. Our system have to receive data file from previous...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 30, 2015 at 10:51 am
You could consider clustering the table on the datetime involved. Then you can delete old data with minimal effect on current rows, particularly by reducing the number of non-clustered...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 30, 2015 at 10:44 am
I've also seen vast numbers of errors created by explicit conversions. If you're forced to deal with temporal data stored as char/varchar, then store it in a universal format...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 30, 2015 at 8:07 am
Sorry:
WHERE (@IsProcessed IS NULL OR @IsProcessed = <table_column>)
Edit: general pattern, you may need to change it slightly.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 27, 2015 at 3:45 pm
WHERE IsProcessed IS NULL
If you want to check for new rows to process while existing rows are being processed, look into the "READPAST" hint.
If you just need to quickly find...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 27, 2015 at 3:13 pm
Eirikur Eiriksson (3/27/2015)
ScottPletcher (3/27/2015)
Just add them and SQL will implicitly convert the varchar to a datetime anyway:SELECT datetime_column + time_varchar AS new_datetime, ...
FROM ...
Until the locale driven implicit conversion starts...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 27, 2015 at 11:06 am
Viewing 15 posts - 5,251 through 5,265 (of 7,619 total)