Viewing 15 posts - 5,476 through 5,490 (of 7,613 total)
You don't need to re-count the rows every time.
DECLARE @BatchSize int
DECLARE @RowRount int
DECLARE @TableRowCount int
DECLARE @TableRowLimit int
SET @BatchSize = 10000
SELECT @TableRowCount = COUNT(*)
FROM PtActs WITH (NOLOCK)
SET @TableRowLimit = 10050000
WHILE...
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".
January 13, 2015 at 10:45 am
The VALUES clause simplifies it considerately, which can be great for longer lists:
CROSS APPLY (
SELECT MIN(date)
FROM (
...
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".
January 9, 2015 at 3:39 pm
SQLSACT (1/9/2015)
(SELECT plant_nbr
FROM ProductPlants AS B
WHERE B.sku =...
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".
January 9, 2015 at 2:48 pm
DECLARE @sql varchar(max)
DECLARE @column_list varchar(max) --as returned by first query
SET @column_list = 'age,sex,race' --for example
SET @sql = 'UPDATE tbl2 SET col2 = [' + REPLACE(@column_list, ',', ']+[') + ']'
PRINT...
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".
January 9, 2015 at 2:42 pm
I suspect recursion would be great for this, but that's not my specialty. This is somewhat akin to the earlier LOJ version, which unfortunately I didn't see until now...
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".
January 9, 2015 at 2:38 pm
Eric M Russell (1/9/2015)
ScottPletcher (1/8/2015)
SELECT
CASE
WHEN message_text LIKE...
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".
January 9, 2015 at 9:16 am
GilaMonster (1/9/2015)
Table is empty. No rows in the table means that the statistics objects are empty.
Not necessarily true -- a given index can be empty even if the table is...
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".
January 9, 2015 at 9:09 am
You could also GROUP directly on the CASE expression itself:
SELECT
CASE
WHEN message_text LIKE '%ThePartToGroupBy%' THEN '%ThePartToGroupBy%'
...
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".
January 8, 2015 at 4:55 pm
Might as well have max flexibility on the number of name levels provided:
DECLARE @tablename varchar(500)
DECLARE @sql varchar(8000)
SET @tablename = 'YourTableName'
--SET @tablename = 'server1.db1..YourTableName'
SET @sql = 'SELECT * FROM '...
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".
January 8, 2015 at 4:48 pm
SELECT
DD1.[Incident_x0020_Date] AS [Incident Date],
1 AS [Order],
DD1.[Int_x002f_Dom] AS [Location],
DD1.[ACTION] AS [Action],
DD1.[Channel] AS [Channel Type],
SUM(CASE WHEN DD1.[DLP_x0020_VIOLATION] = 'Bribery & Corruption' THEN 1 ELSE 0 END)...
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".
January 8, 2015 at 4:41 pm
Note that you can also create non-temp tables in the tempdb database. That way the table will be there even if the connection that created it "gets broken" or...
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".
January 8, 2015 at 4:33 pm
The index is empty, i.e., there are no rows in that index.
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".
January 8, 2015 at 4:30 pm
SELECT sm.*, tv_lookup.Amount AS tv_Amount, tv_lookup.PercRate AS tv_PercRate, tv_lookup.Code AS tv_Code
FROM #SM sm
OUTER APPLY (
SELECT TOP (1) *
FROM #TestValues tv
...
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".
January 8, 2015 at 3:54 pm
Jeff Moden (1/8/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".
January 8, 2015 at 1:54 pm
I'm almost certain the optimizer is sophisticated enough to implicitly convert the literal value rather than a variable value or a column.
If you're still concerned about it, you could do...
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".
January 7, 2015 at 1:01 pm
Viewing 15 posts - 5,476 through 5,490 (of 7,613 total)