Viewing 15 posts - 6,421 through 6,435 (of 7,619 total)
Sean Lange (8/30/2013)
ScottPletcher (8/30/2013)
NOLOCK can be useful, for example, for code lookup tables, such as state code lookups. A state hasn't been added since 1959, I think I'll risk...
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".
August 30, 2013 at 2:17 pm
Sean Lange (8/30/2013)
ScottPletcher (8/30/2013)
Sean Lange (8/30/2013)
ScottPletcher (8/30/2013)
Sean Lange (8/30/2013)
NOLOCK hints. They are NOT a performance boost.
To me, that's not a logical claim. While NOLOCK is clearly dangerous, it DOES...
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".
August 30, 2013 at 2:02 pm
MyDoggieJessie (8/30/2013)
It may "bypass" the locking of the records it's ready but it does not improve performance or reduce any overhead one bit.
LOL. That's self-contradictory -- if NOLOCK avoids...
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".
August 30, 2013 at 2:00 pm
Sean Lange (8/30/2013)
ScottPletcher (8/30/2013)
Sean Lange (8/30/2013)
NOLOCK hints. They are NOT a performance boost.
To me, that's not a logical claim. While NOLOCK is clearly dangerous, it DOES in fact reduce...
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".
August 30, 2013 at 1:52 pm
You should be able to use a correlated subquery, like so:
SELECT
@mystring,
REPLACE(@mystring, name, value) AS newstring
FROM (
SELECT TOP...
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".
August 30, 2013 at 1:49 pm
Here's a quick-and-dirty first try -- might or might not work. Also, you need to get rid of the DISTINCT if at all possible -- it's a big drag...
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".
August 30, 2013 at 1:34 pm
Sean Lange (8/30/2013)
NOLOCK hints. They are NOT a performance boost.
To me, that's not a logical claim. While NOLOCK is clearly dangerous, it DOES in fact reduce overhead by avoiding...
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".
August 30, 2013 at 1:26 pm
kapil_kk (8/30/2013)
ScottPletcher (8/29/2013)
ALTER TABLE dbo.tblemployeeADD ident int IDENTITY(1, 1) NOT NULL
Edit: Added "NOT NULL"
You are adding a new column with IDENTITY property and it works..
The thing is that we cant...
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".
August 30, 2013 at 9:53 am
Float only takes 4 bytes to store if you specify a precision of 24 or less.
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".
August 29, 2013 at 4:48 pm
WHY IS THE LEN OF THE FLOAT 4?!
It takes 4 bytes to store a float value (up to 24 digits).
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".
August 29, 2013 at 4:28 pm
DOH, just realized, the proc should check for an explicitly specified schema name and parse out the names before adding brackets:
alter PROC [dbo].[sp_getrecords]
(
@TableName sysname
)
AS
declare @SchemaName sysname
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".
August 29, 2013 at 3:55 pm
Sean Lange (8/29/2013)
ScottPletcher (8/29/2013)
create PROC [dbo].[sp_getrecords]
(...
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".
August 29, 2013 at 3:35 pm
I was thinking of creating a script file and calling that from a stored procedure on every database?
Yes, that should work, although it's a lot of extra work and...
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".
August 29, 2013 at 3:32 pm
Jay Purvis (8/29/2013)
Especially when the builds were released only a month...
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".
August 29, 2013 at 3:29 pm
if my statement that I started with begin Tran errors out, will that transaction stay open till I do rollback or commit?
Yes. Once a transaction begins, it must either...
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".
August 29, 2013 at 3:26 pm
Viewing 15 posts - 6,421 through 6,435 (of 7,619 total)