Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Time Bomb Design - A Longer Fuse Expand / Collapse
Author
Message
Posted Tuesday, May 25, 2010 10:46 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:45 PM
Points: 2,892, Visits: 1,784
Comments posted to this topic are about the item Time Bomb Design - A Longer Fuse

LinkedIn Profile
Newbie on www.simple-talk.com
Post #927945
Posted Tuesday, May 25, 2010 11:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, December 4, 2011 10:06 AM
Points: 2, Visits: 9
Other activity on my PC meant that I was a victim of cursor lag (the blinking cursor, not the SQL one). As a result, when I clicked on what I thought was 5 stars, I actually gave 1 star. Sorry.

Excellent article, with the part about Cinderella users ringing so true. "Rats nest of Excel spreadsheets" indeed!
Post #927953
Posted Wednesday, May 26, 2010 12:35 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 25, 2014 1:47 AM
Points: 1,203, Visits: 744
sporran (5/25/2010)
As a result, when I clicked on what I thought was 5 stars, I actually gave 1 star.


Added one star extra. If 3 other persons will do that...

Good article.




Dutch Anti-RBAR League
Post #927969
Posted Wednesday, May 26, 2010 12:45 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, July 19, 2013 2:44 AM
Points: 77, Visits: 201
I clicked on 5 stars...something out there decided that should be 4!

I'll be sharing this with a few of my colleagues - I really hope they take the not-so-subtle hint!!
Post #927973
Posted Wednesday, May 26, 2010 2:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 23, 2012 3:28 AM
Points: 1, Visits: 26
Great article - thank you for writing it!
Post #928024
Posted Wednesday, May 26, 2010 2:45 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 10, 2014 7:24 AM
Points: 1,060, Visits: 874
Excellent article, thanks for sharing. I look forward to the next installment!

If such a facility existed I would suggest it be pinned, and enshrined in the hall of fame.

However, if you were to write and publish a version of this aimed at SQL Developers it would make a lot of DBA's lives a lot easier.
As it stands forwarding this to the Devs as a standalone might bite back... ;)

Cheers

Rich




Post #928033
Posted Wednesday, May 26, 2010 3:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 18, 2011 6:39 AM
Points: 1, Visits: 69
Your "Cinderella Data Users" sub heading made me laugh.. I work for a huge organization and work in such a department and only hope someone higher up the food chain here reads your article and takes note.

M$ Office/SQL Express are the only tools available to me, I have projects with deadlines that need meeting just like the heavy hitting DBAs in the organization - I'm sure that this will lead to problems for them should I ever leave but seriously, the support isn't there for me now so I must do what I must.

Excellent article, Thanks!
Post #928039
Posted Wednesday, May 26, 2010 4:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:56 PM
Points: 11,192, Visits: 11,096
David,

I just scanned through on a first read, but something important caught my eye:

In the section on changing data types (after rebuilding the clustered index) you say that for SQL Server 2005: "Fixed length datatype sizes are imutable. Changes are only at the metadata level."

This is not so.

Rebuilding the clustered index does indeed reclaim the space. There is a simplified script at the end of this post to demonstrate. I have also tried your script (with my own ALTER TABLE ALTER COLUMN statements) and received the same results.

These are the statements I used with your script:
ALTER TABLE dbo.FinancialStatsLarge ALTER COLUMN AnnualInterestRate DECIMAL(5,2) NOT NULL;
ALTER TABLE dbo.FinancialStatsLarge ALTER COLUMN MonthlyInterestRate DECIMAL(5,2) NOT NULL;
ALTER TABLE dbo.FinancialStatsLarge ALTER COLUMN AnnualFee DECIMAL(9,2) NOT NULL;
ALTER TABLE dbo.FinancialStatsLarge ALTER COLUMN MonthlyFee DECIMAL(9,2) NOT NULL;
ALTER TABLE dbo.FinancialStatsLarge ALTER COLUMN AnnualCost DECIMAL(9,2) NOT NULL;
ALTER TABLE dbo.FinancialStatsLarge ALTER COLUMN MonthlyCost DECIMAL(9,2) NOT NULL;
GO
ALTER INDEX [PK_FinancialStatsLarge] ON dbo.FinancialStatsLarge REBUILD;

Demo:
CREATE  TABLE dbo.Immutable 
(
row_id INTEGER NOT NULL
IDENTITY (1,1)
CONSTRAINT [PK dbo.Immutable row_id] PRIMARY KEY,
data DECIMAL(18,2) NOT NULL);

INSERT dbo.Immutable (data) VALUES (1.23);

-- Show table page data
DBCC IND (0, Immutable, 1);

-- Redirect DBCC PAGE output from the error log to the client window
DBCC TRACEON(3604);

-- Page 14830 in file 1 was the data page on my test run
DBCC PAGE (0, 1, 15162, 3);

--====================================
-- DBCC PAGE EXTRACT
--====================================
--
-- Slot 0 Offset 0x60 Length 20
--
-- Slot 0 Column 0 Offset 0x4 Length 4 row_id = 1
-- Slot 0 Column 1 Offset 0x8 Length 9 data = 1.23
--
--====================================

-- Alter the column definition
-- Metadata-only change at this stage
ALTER TABLE dbo.Immutable
ALTER COLUMN data DECIMAL(5,2) NOT NULL;

-- Rebuild the clustered index
ALTER INDEX [PK dbo.Immutable row_id] ON dbo.Immutable REBUILD;

-- The data page allocated will have changed
DBCC IND (0, Immutable, 1);

-- Data now on page 15163 in file 1 on my test run
DBCC PAGE (0, 1, 15163, 3);

--====================================
-- DBCC PAGE EXTRACT
--====================================
--
-- Slot 0 Offset 0x60 Length 16
--
-- Slot 0 Column 0 Offset 0x4 Length 4 row_id = 1
-- Slot 0 Column 1 Offset 0x8 Length 5 data = 1.23
--
-- Notice the record as a whole is 4 bytes shorter
-- and column 1 is *physically* 5 bytes now
--
--====================================

DROP TABLE dbo.Immutable;
DBCC TRACEOFF (3604);





Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #928094
Posted Wednesday, May 26, 2010 5:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 26, 2014 12:53 PM
Points: 5, Visits: 38
"Cinderella data users"

Very aptly stated.
This is where I live.
Every day.
Nice article.
Post #928138
Posted Wednesday, May 26, 2010 6:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, March 4, 2012 9:49 AM
Points: 10, Visits: 49
Nicely, nicely done. Six stars.
Post #928186
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse