Viewing 15 posts - 6,616 through 6,630 (of 7,613 total)
The advantage to tempdb is that there is less overhead writing data there since SQL "knows" it never has to recover tempdb.
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 29, 2013 at 4:13 pm
That seems like a reasonable approach. Using REPLICATE() will make it easier to see how many numbers are in the pattern and/or adjust it later:
WHERE PATINDEX('%[0-9]' + REPLICATE('[0-9 -]',...
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 29, 2013 at 3:40 pm
Note that you can use a "real" table name in tempdb; that is, a table name that does not begin with # or ##. The table will then remain...
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 29, 2013 at 3:19 pm
Until you get to SQL 2012, you also have the "quick-and-dirty" version of that, using COMPUTE:
SELECT Curr_date, Action, AVG(Duration)/1000 as Avg_Duration
FROM Table_1
WHERE Curr_date = CAST(GETDATE() as DATE)
GROUP BY Curr_date,...
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 29, 2013 at 9:13 am
Grant Fritchey (3/28/2013)
ScottPletcher (3/28/2013)
For single row lookups, this is not really a performance hit.
But for table scans, even...
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 28, 2013 at 8:55 am
Also, be sure to check: sys.sql_expression_dependencies.
And not just in its own db but in other dbs, esp. on the same instance.
And the usual disclaimer: even that isn't guaranteed to be...
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 28, 2013 at 8:49 am
There must be more fragmentation in a history table than a current-data-only table.
For single row lookups, this is not really a performance hit.
But for table scans, even partial ones, it...
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 28, 2013 at 7:28 am
EXEC sp_MSforeachdb '
IF NOT EXISTS(SELECT 1 FROM [?].sys.tables WHERE name = N''myTable'')
RETURN
SELECT ''?'' AS Db_Name, *
FROM [?]..mytable
'
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, 2013 at 11:11 am
My best guess on the very limited info so far is something like this:
select MonthName, DocName, count(distinct day(appointment_date)) as days_worked
from Appointments
group by MonthName, DocName
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, 2013 at 8:57 am
You could easily use master for that. Especially given that you must use master for some procedures anyway. Moreover, it's much easier from SQL2005 on to distinguish your...
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, 2013 at 8:52 am
sp_MSforeachdb is indeed a cursor, and not a particularly good one.
Still, it exists, and it's useful for quick-and-dirty tasks sometimes, particularly if you don't have a lot of dbs.
So, I'll...
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 26, 2013 at 4:25 pm
Sean Lange (3/26/2013)
nitin_456 (3/25/2013)
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 26, 2013 at 11:09 am
The main table would still hold the entire current record. Even if the history table contains the current record, you don't want to get it from there because of...
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 26, 2013 at 10:04 am
I don't see why it would necessarily be that bad, at least for a straightforward "row must match the username" check. I've done these a few times w/o any...
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 25, 2013 at 3:48 pm
And, finally, you don't really need SUBSTRING, LEFT is good enough :-):
SELECT @string, LEFT(@string, LEN(@string) - CHARINDEX('\', REVERSE(@string)) + 1)
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 25, 2013 at 3:46 pm
Viewing 15 posts - 6,616 through 6,630 (of 7,613 total)