Viewing 15 posts - 2,461 through 2,475 (of 7,619 total)
I don't believe the inserted table (view), which is current data, could be created from a version store, which is historical data.
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 3, 2020 at 3:31 pm
First, can someone speak to the premise that the entire row is updated when updating one column?
No. Only the modified column(s) are updated.
We can certainly prove that 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".
March 3, 2020 at 3:16 pm
My inclination would be to move the char(7200) to a different table. Or, depending on the specific circumstances, change it to varchar(max) and force it LOB (out of the main...
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 2, 2020 at 10:25 pm
First, can someone speak to the premise that the entire row is updated when updating one column?
No. Only the modified column(s) are updated.
Is this a good suggestion? What are the...
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 2, 2020 at 9:43 pm
Is the view reference directly in the code or is it some type of generated / dynamic SQL? Naturally the view can only give references for the code that is...
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 2, 2020 at 9:30 pm
Not perfect, but this should get you much closer:
SELECT
ReferencingObjectType = o1.type,
ReferencingObjectName = o1.type_desc,
ReferencingObject = SCHEMA_NAME(o1.schema_id)+'.'+o1.name,
ReferencedObject = ISNULL(ed.referenced_database_name, DB_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".
March 2, 2020 at 8:42 pm
I'm a bit confused about "Return values" being plural. Isn't there only a "return value"? Yes, somewhat pedantic mood as well :-).
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".
February 28, 2020 at 6:55 pm
I could think of 4 rather easily, I'm sure there's more, as others have noted.
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".
February 28, 2020 at 3:28 pm
Be sure to specify "WITH (TABLOCK)" on the table being loaded. It's not technically needed at 2016+ level but it doesn't hurt.
The very first page of a table that SQL...
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".
February 28, 2020 at 3:01 pm
If you don't have permission, you won't have permission period, but the command-line way to get the text is:
EXEC sys.sp_helptext 'view_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".
February 28, 2020 at 2:53 pm
Here's the main article I was basing my comments on about minimal logging in SQL Server 2016:
https://sqlperformance.com/2019/05/sql-performance/minimal-logging-fast-load-context
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".
February 26, 2020 at 10:11 pm
SQL Server 2016 does allow far more minimally-logged INSERTs to an existing clustered index than any earlier version of SQL.
And, in some instances at least, it does allow simultaneous INSERTs...
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".
February 26, 2020 at 9:48 pm
I'm not sure. I don't know if SQL can do parallel writes to a new clustered index.
Given all the possible table options -- data compression, large value types out 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".
February 26, 2020 at 9:21 pm
I think it could.
I'd certainly expect the initial SELECTs from the table to be done in parallel, and thus likely the sorting as well. But the writes to the final...
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".
February 26, 2020 at 8:57 pm
Try using datetime as the destination data type, rather than smalldatetime.
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".
February 26, 2020 at 5:45 pm
Viewing 15 posts - 2,461 through 2,475 (of 7,619 total)