Viewing 15 posts - 4,141 through 4,155 (of 7,619 total)
You don't need a global temp table, a regular temp table will do.
Create the table in main stored proc, then ALTER it using dynamic SQL. Since the temp 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".
September 27, 2016 at 1:59 pm
Talib123 (9/27/2016)
I'm a production DBA
... how do I get my T-sql skills to a developer\Report writer level.
So you want to go from DBA-level SQL to developer-level SQL:
Destroy half your brain...
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".
September 27, 2016 at 11:23 am
I can't think of a way right now for that to work using a table variable.
Can you use a temp table instead?
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".
September 27, 2016 at 10:32 am
Eric M Russell (9/26/2016)
ScottPletcher (9/26/2016)
Eric M Russell (9/26/2016)
ScottPletcher (9/23/2016)
Eric M Russell (9/23/2016)
Regardless of SQL Server version, performance optimization should start by looking at execution plans.
Not necessarily. If the best...
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".
September 26, 2016 at 11:04 am
First, cluster the tables for best (overall) performance. In particular, don't assume that every table should be clustered by identity. That's most often simply not the best way...
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".
September 26, 2016 at 10:42 am
Eric M Russell (9/26/2016)
ScottPletcher (9/23/2016)
Eric M Russell (9/23/2016)
Regardless of SQL Server version, performance optimization should start by looking at execution plans.
Not necessarily. If the best clustered indexes are 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".
September 26, 2016 at 9:47 am
Eric M Russell (9/23/2016)
Regardless of SQL Server version, performance optimization should start by looking at execution plans.
Not necessarily. If the best clustered indexes are not already in place, you...
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".
September 23, 2016 at 3:07 pm
CREATE TABLE #temp1_dup_emails (email varchar(20) PRIMARY KEY);
Insert into #temp1_dup_emails
SELECT CASE WHEN which_email = 1 THEN email1 ELSE email2 END AS email
FROM #temp1
CROSS JOIN (
VALUES(1),(2)
) AS which_email(which_email)
GROUP...
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".
September 23, 2016 at 3:04 pm
Personally I wouldn't rely on sys.partitions to gauge whether a table is completely empty or not. Maybe just me. Just out of curiosity, what if a table load...
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".
September 23, 2016 at 1:03 pm
If it's not a system db, you can move db files by specifying a new location for the file, setting the db offline, physically moving the file(s), and finally setting...
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".
September 23, 2016 at 12:29 pm
No. But maybe you could create your own view with a similar name -- such as dbo.sysusers2 -- and change your code to use the view name?
create view dbo.sysusers2...
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".
September 23, 2016 at 12:26 pm
I view it differently, since I prefer a code-based solution to a gui-based solution.
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".
September 22, 2016 at 10:55 am
Here's the code you're not looking for 😉
/* drop empty "MyTable%" tables */
DECLARE @exec_sql bit
DECLARE @print_sql bit
DECLARE @sql nvarchar(4000)
DECLARE @table_has_row bit
DECLARE @table_name nvarchar(128)
SET @exec_sql = 0
SET @print_sql = 1
DECLARE cursor_tables...
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".
September 22, 2016 at 10:24 am
Also, be aware that some plans are never cached at all, for example, a trivial plan to satisfy "select * from table_name".
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".
September 22, 2016 at 10:02 am
Sergiy (9/21/2016)
On different server settings you may get different results:
SET DATEFORMAT DMY
SELECT CONVERT(VARCHAR(20), CONVERT(datetime,CAST('2016-08-10 05:29:27' as datetime),112),...
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".
September 22, 2016 at 8:26 am
Viewing 15 posts - 4,141 through 4,155 (of 7,619 total)