Viewing 15 posts - 4,651 through 4,665 (of 7,619 total)
Sergiy (11/16/2015)
ScottPletcher (11/16/2015)
But there is still no reason whatsoever to cluster this table by identity.
Actually, there is.
And I pointed on it in my previous post:
to preserve...
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".
November 17, 2015 at 7:54 am
Sergiy (11/16/2015)
ScottPletcher (11/16/2015)
[Yes, (maybe) that gives you less fragmentation on the base table, but that's irrelevant if you (almost) never look up by the id anyway!!
I'd say it this...
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".
November 16, 2015 at 5:09 pm
Arjun Sivadasan (11/16/2015)
ScottPletcher (11/16/2015)
If the table does not exist at all, SQL defers name resolution ("deferred name resolution") for that table.<snip/>
Thanks for the reply Scott. If a table does 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".
November 16, 2015 at 4:19 pm
Sergiy (11/16/2015)
ScottPletcher (11/16/2015)
but still cluster the table on the RedemptionCode.Clustering a column with randomly generating values is not the best approach ever.
That's a drastic overstatement. Remember, the table...
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".
November 16, 2015 at 2:50 pm
The best solution is to tune the existing indexes, starting with verifying that you have the best clustered index on every table. Btw, empty/unused space within the db will...
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".
November 16, 2015 at 12:59 pm
I don't think SQL will have to use an implicit conversion for varchar to char, since it pads spaces when doing character comparisons anyway. Not 100% sure, so I'd...
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".
November 16, 2015 at 12:41 pm
If the table does not exist at all, SQL defers name resolution ("deferred name resolution") for that table. That is, it waits until run time, then checks to see...
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".
November 16, 2015 at 12:29 pm
pollokoff (11/13/2015)
However, using [IS NULL] does not result in the use of the index that exists on the EXPIRATION column.
...
The question was more for general knowledge. The actual query...
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".
November 16, 2015 at 12:26 pm
Brandie Tarvin (11/13/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".
November 13, 2015 at 1:11 pm
Sorry, I should have been clearer on that point!
DelimitedSplit8K is an inline table-valued function for SQL Server, but it's not part of SQL itself, it was developed by users. ...
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".
November 13, 2015 at 9:20 am
Best is to use a very efficient splitter, such as DelimitedSplit8K, and then do an INNER JOIN to those results:
DECLARE @custID varchar(200)
set @custID = '72793,60546,91069'
SELECT tn.*
FROM table_name tn
INNER JOIN dbo.DelimitedSplit8K...
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".
November 12, 2015 at 3:13 pm
He wants to do nothing himself. I tried to walk him thru the logic of how to code #4 in his original q, here:
http://forums.sqlteam.com/t/need-help-case-statement-should-work/4070
but he refused, instead basically just...
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".
November 12, 2015 at 2:57 pm
Explicitly set it ON immediately before DELETE/INSERT/UPDATE statements and OFF immediately afterward?
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".
November 12, 2015 at 12:36 pm
You can also do it with just a single scan of the data table:
SELECT ca1.*
FROM table_name
CROSS APPLY (
VALUES(Car, ColorOption1),(Car, ColorOption2),(Car, ColorOption3)
) AS ca1(Car,ColorOption)
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".
November 12, 2015 at 12:33 pm
I wouldn't use the gui for that, although, to be fair, the gui usually does ok on that particular task. But it's so relatively flaky on others that I...
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".
November 11, 2015 at 3:14 pm
Viewing 15 posts - 4,651 through 4,665 (of 7,619 total)