Viewing 15 posts - 5,461 through 5,475 (of 7,619 total)
I still see no reason to risk @@ROWCOUNT if you just want to know if a row was modified or not -- that easy enough to check with EXISTS(), which...
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 19, 2015 at 2:03 pm
Jeff Moden (1/19/2015)
ScottPletcher (1/19/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 19, 2015 at 1:49 pm
Do not use INFORMATION_SCHEMA.* views in SQL Server. They are very slow compared to sys.* views and seem to cause much more locking/deadlocking. Based on research, it seems...
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 19, 2015 at 9:29 am
@@ROWCOUNT is no longer reliable in triggers and should not be used to determine if/how many rows were affected. You have an easy alternative anyway, since you just want...
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 19, 2015 at 9:26 am
You could check for ascii 0 in a the column like so:
WHERE
col1 LIKE '%' + CHAR(0) + '%'
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 16, 2015 at 3:18 pm
TheSQLGuru (1/16/2015)
Where ltrim(rtrim(col1)) = ''I see this code ALL THE TIME at clients. Can someone tell me why both would be needed?? 😎
Why would either be "needed"? 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".
January 16, 2015 at 3:16 pm
halifaxdal (1/16/2015)
Lowell (1/16/2015)
halifaxdal (1/16/2015)
no way i know of to ignore errors, you have to explicitly use LEFT functions in the SELECT or something to work around this issue.
based on 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".
January 16, 2015 at 3:11 pm
ALTER TABLE dbo.person_test2
ADD CONSTRAINT CK_ext
CHECK ( Pext LIKE REPLICATE('[0-9]', LEN(Pext)) )
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 16, 2015 at 3:06 pm
You really shouldn't need full securityadmin just to grant EXECUTE on a proc(s):
GRANT EXECUTE TO user1 WITH GRANT OPTION
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 16, 2015 at 3:01 pm
FILLFACTOR directs SQL on how full to make each page of data. 96% leaves 4% -- roughly 300 bytes -- free on each page to allow for new rows...
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 16, 2015 at 12:34 pm
Technically it should be left at 0, as that's more secure.
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 15, 2015 at 3:46 pm
The ROLLBACK could indeed cause an error if a transaction wasn't active at the time. Here's how to correct that:
IF XACT_STATE() <> 0
BEGIN
ROLLBACK;
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 15, 2015 at 3:46 pm
You can use a PK or simply a UNIQUE constraint or index. For example:
CREATE UNIQUE CLUSTERED INDEX Student_Classes__CL
ON dbo.Student_Classes ( Student_ID, Class_ID ) WITH...
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 15, 2015 at 3:43 pm
T_Peters (1/14/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 14, 2015 at 4:28 pm
Or even another view. A view could also explicitly reference objects in a different db.
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 14, 2015 at 4:24 pm
Viewing 15 posts - 5,461 through 5,475 (of 7,619 total)