Viewing 15 posts - 5,341 through 5,355 (of 7,619 total)
As I noted earlier, once you have data loaded you can do:
SELECT *
FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('<your_table_name>'),NULL,NULL,'DETAILED')
And SQL will give you the average and max rows lengths for the data you have.
My script...
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, 2015 at 9:07 am
Jeff Moden (2/27/2015)
ScottPletcher (2/27/2015)
Jeff Moden (2/27/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".
February 27, 2015 at 4:47 pm
Jeff Moden (2/27/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".
February 27, 2015 at 4:21 pm
Jeff Moden (2/27/2015)
ScottPletcher (2/27/2015)
Jeff Moden (2/27/2015)
ScottPletcher (2/27/2015)
Jeff Moden (2/27/2015)
ScottPletcher (2/27/2015)
Your clustered index key(s) doesn't(don't) have to be unique by itself(themselves); SQL will take care of that if it needs 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".
February 27, 2015 at 4:00 pm
Jeff Moden (2/27/2015)
ScottPletcher (2/27/2015)
Jeff Moden (2/27/2015)
ScottPletcher (2/27/2015)
Your clustered index key(s) doesn't(don't) have to be unique by itself(themselves); SQL will take care of that if it needs to.
... at possibly quite...
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 27, 2015 at 3:50 pm
Jeff Moden (2/27/2015)
ScottPletcher (2/27/2015)
Your clustered index key(s) doesn't(don't) have to be unique by itself(themselves); SQL will take care of that if it needs to.
... at possibly quite some cost if...
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 27, 2015 at 3:02 pm
You can also use CROSS APPLY to do that:
select VC.COLUMN_NAME,
case when
ROW_NUMBER () OVER (
partition by C.COLUMN_NAME order by
CHARINDEX(',',VIEW_DEFINITION,CHARINDEX(C.COLUMN_NAME,VIEW_DEFINITION))-
CHARINDEX(VC.COLUMN_NAME,VIEW_DEFINITION)
) = 1
then 1
else 0 end
as lenDiff
,alias
,CHARINDEX(',',VIEW_DEFINITION,CHARINDEX(C.COLUMN_NAME,VIEW_DEFINITION)) diff1
...
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 27, 2015 at 2:24 pm
D'OH!
Here's my script, based on Books Online's description of how to calc max row size. I think it will give you a reasonably good estimate of the max possible...
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 27, 2015 at 2:12 pm
Yep. To me, the RAM restrictions and the lack of data compression are the big factors in Standard Edition. Snapshots not being available isn't that bad, although 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".
February 27, 2015 at 1:59 pm
Just let SQL tell you :-).
Create an empty table and then run this command on it:
SELECT *
FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('<your_table_name>'),NULL,NULL,'DETAILED')
It will give you the min and max row sizes.
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 27, 2015 at 1:52 pm
djj (2/27/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".
February 27, 2015 at 1:20 pm
Eric M Russell (2/27/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".
February 27, 2015 at 1:16 pm
Before making such a critical choice, review SQL's missing index and index usage stats. Odds are, you have a better choice than an identity column for the clustering index...
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 27, 2015 at 12:50 pm
Eric M Russell (2/27/2015)
N_Muller (2/26/2015)
...
To note is the fact that the table(s) will have millions of rows, but the customer will request data for at at most, 100 or so...
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 27, 2015 at 12:47 pm
Eric M Russell (2/27/2015)
ScottPletcher (2/27/2015)
A lower FILLFACTOR might be required. Since users always provide the up-to-50-byte key, the idea is to avoid having to create nonclustered indexes. This...
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 27, 2015 at 12:45 pm
Viewing 15 posts - 5,341 through 5,355 (of 7,619 total)