Viewing 15 posts - 4,651 through 4,665 (of 7,613 total)
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
Hmm. Typically one of the main things to address deadlocking is adjusting indexes. And the first priority in tuning is general is always to get the best 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".
November 11, 2015 at 9:20 am
There are certain changes that could make recreating the table more efficient than just the ALTERs. Would need details of the specific change(s) to know for sure. It's...
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 8:52 am
How many unique UnitIds are there? It looks as if the clustering key on the table should be ( UnitId, StartDate, Id ) [Id is optional, just to make...
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 8:49 am
Sergiy (11/10/2015)
David Rich (11/9/2015)
Type conversion in expression (CONVERT_IMPLICIT(varchar(255),[e].[abc],0)) may affect "CardinalityEstimate" in query plan choice
and this is getting converted to nvarchar(255)....
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 10, 2015 at 1:43 pm
David Rich (11/9/2015)
Type conversion in expression (CONVERT_IMPLICIT(varchar(255),[e].[abc],0)) may affect "CardinalityEstimate" in query plan choice
and this is getting converted to nvarchar(255). 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 10, 2015 at 11:42 am
Yet another version, as an in-line table-valued function, for easy use within a query:
CREATE FUNCTION dbo.fn_first_day_and_last_day_of_week
(
@year smallint,
@week tinyint
)
RETURNS...
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 10, 2015 at 9:31 am
Viewing 15 posts - 4,651 through 4,665 (of 7,613 total)