Viewing 15 posts - 4,366 through 4,380 (of 7,619 total)
Alan.B (3/29/2016)
Lastly, ditto the last Thursday of the month:
SET STATISTICS IO ON;
PRINT 'Old way:';
SELECT DISTINCT MAX([Date]) OVER (PARTITION BY [Year],[MonthName],[WeekdayName]) AS [Date]
FROM dbo.itvf_CALENDAR('01/01/2014','12/31/2014')
WHERE [WeekdayName]...
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 30, 2016 at 8:39 am
SELECT DATENAME(MONTH, CAST('01-' + string AS date)) + SPACE(1) +
CAST(YEAR(CAST('01-' + string AS date)) AS varchar(4))
FROM (VALUES('Dec-15'),('Jan-16'),('Feb-16')) test_data(string)
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, 2016 at 9:13 am
A couple of things to consider.
1) Change the PRINT @<sql_variable> to SELECT @<sql_variable>, since SELECT can display many more chars than SELECT.
2) Explicitly cast the unicode literal in the SET...
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, 2016 at 9:07 am
You could create a logon trigger to capture which logins are being used, and how often.
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, 2016 at 1:05 pm
23:59:59.000 does not really make sense in SQL Server, since it doesn't match any system data type's time sensitivity.
Smalldatetime is 23:59 and datetime is 23:59:59.997.
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, 2016 at 1:04 pm
Use a tally table -- a table of just sequential numbers -- to generate the months. You can use an in-line tally table or a stored tally 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".
March 28, 2016 at 11:53 am
Are you certain that query is the one that is deadlocking? So that query wasn't really commented out in the proc?!
Do you have on trace flags, at least 1222,...
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 24, 2016 at 9:32 am
And prefix every column in the query with a table alias. Remember, we have no idea what columns are in what tables.
Which table is "ID" in?
Which table is "NO_OF_PIN"...
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 24, 2016 at 9:19 am
mceventphoto (3/23/2016)
Where b.ITEMCODE = @itemCode
with
Where...
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 23, 2016 at 3:00 pm
Sure, you can do it, but if you post the code it's extremely likely we here can re-work the entire method being used and make it much better.
create table #values...
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 23, 2016 at 10:05 am
The year, the 3-digit number and the incrementing number should all be stored as separate columns. You then use a computed column to construct the rptnum column. That...
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 22, 2016 at 11:12 am
Excellent article! My only additional suggestion would be to put your code in the master db, and start the proc name with sp_, so that the code can easily...
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 22, 2016 at 10:35 am
edward_hall76 (3/21/2016)
DBA or developer.
Hmm, overall vastly different qs, but if they are supposed to have experience, here's one q I love to ask ... but give them some time to...
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 22, 2016 at 9:37 am
Don't see anything better at a relatively quick look, although I would get rid of the double negative logic:
SELECT *
FROM #OrderDetails OD
WHERE OD.ActionCode = 'V' 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".
March 18, 2016 at 4:06 pm
Sergiy (3/17/2016)
TheSQLGuru (3/17/2016)
1) using a temp table (NOT table variable!!)Not sure if there is much of a difference here.
Scoping - yes, quite different.
Can you name anything else?
You can...
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 18, 2016 at 9:35 am
Viewing 15 posts - 4,366 through 4,380 (of 7,619 total)