SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Time Bomb Design - A Longer Fuse


Time Bomb Design - A Longer Fuse

Author
Message
Dave Poole
Dave Poole
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24191 Visits: 3487
Comments posted to this topic are about the item Time Bomb Design - A Longer Fuse

LinkedIn Profile
www.simple-talk.com
sporran
sporran
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 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!
gserdijn
gserdijn
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1786 Visits: 818
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
AJN
AJN
SSC-Enthusiastic
SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)

Group: General Forum Members
Points: 115 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!!
bbirns
bbirns
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 26
Great article - thank you for writing it!
RichB
RichB
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4260 Visits: 1075
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... Wink

Cheers

Rich



dan.browne
dan.browne
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 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!
Paul White
Paul White
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54576 Visits: 11391
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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
murph_32952
murph_32952
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 39
"Cinderella data users"

Very aptly stated.
This is where I live.
Every day.
Nice article.
jrbirdman
jrbirdman
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 49
Nicely, nicely done. Six stars.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search