FTP necessary?

We will be using connecting our 2 sites through 2
firewalls, and having push subscribers.
I'm new to this type of network infrastructure so please
excuse my naive questions
(1) does this mean that we will be using a VPN?
(2) Do we have to use FTP for initialization or can we use
the normal fileshare (with/without VPN)?
Many thanks,
BB
1) not necessarily, you could get away with only having ports 1433 and 21
open. A VPN will encrypt communication end to end and will allow you to open
more ports.
2) You do not really want to use the file copy/UNC based method over the
internet as the ports you have to open will expose your server to security
threats. You should use FTP in instances like this, even though FTP isn't
the most secure method either, but it doesn't expose your server to been
exploited. Your data may be readable by hackers however. You can lock this
down to a degree.
If you use a VPN you are protected against hackers.
So with a VPN you don't have to use FTP, without a VPN you should.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"BB" <anonymous@.discussions.microsoft.com> wrote in message
news:1b2601c4a0d7$e843d970$a601280a@.phx.gbl...
> We will be using connecting our 2 sites through 2
> firewalls, and having push subscribers.
> I'm new to this type of network infrastructure so please
> excuse my naive questions
> (1) does this mean that we will be using a VPN?
> (2) Do we have to use FTP for initialization or can we use
> the normal fileshare (with/without VPN)?
> Many thanks,
> BB
sql

Frustrating: Can't get to Provider Options in SQL 2005 Express?

What are you trying to achieve?
Look at the sp_addlinkedserver system procedure.
"Thou shall lear to type. Clicking is the work of the Devil."
-- undisclosed apostle
;)
ML
http://milambda.blogspot.com/Thanks, but I have already used sp_addlinkedserver.
I am trying to set the "allow inprocess" provider options... according to
research in the NG's and BOL (see "Linked Server Properties (Provider
Options Page)"), this is set from a dialog that I am unable to access. I
can't see it when adding a new linked server, editing an existing one, or
right-clicking on the provider in Management Studio.
Any ideas?
Thanks,
Mike
"ML" <ML@.discussions.microsoft.com> wrote in message
news:99625079-C5D6-430C-89AB-8AD8421A2731@.microsoft.com...
> What are you trying to achieve?
> Look at the sp_addlinkedserver system procedure.
> "Thou shall lear to type. Clicking is the work of the Devil."
> -- undisclosed apostle
> ;)
>
> ML
> --
> http://milambda.blogspot.com/|||Hi,
I'm trying to get to the provider options screen in SQL2K5 Management
Studio, however I am unable to find it anywhere. This is really
frustrating... Here is what I am trying to do:
1.. In SQL Mgmt Studio, connect to the SQL Server Database Engine and go
to Server Objects->Linked Server->Providers in the Object Explorer.
2.. Right-click on a provider and select Properties.
However, all I can see when I right-click is "Refresh" ... How can I access
the provider options? Is this a limitation of the Express database? Is there
a stored proc I can use?
Thanks,
Michael|||What are you trying to achieve?
Look at the sp_addlinkedserver system procedure.
"Thou shall lear to type. Clicking is the work of the Devil."
-- undisclosed apostle
;)
ML
http://milambda.blogspot.com/|||Thanks, but I have already used sp_addlinkedserver.
I am trying to set the "allow inprocess" provider options... according to
research in the NG's and BOL (see "Linked Server Properties (Provider
Options Page)"), this is set from a dialog that I am unable to access. I
can't see it when adding a new linked server, editing an existing one, or
right-clicking on the provider in Management Studio.
Any ideas?
Thanks,
Mike
"ML" <ML@.discussions.microsoft.com> wrote in message
news:99625079-C5D6-430C-89AB-8AD8421A2731@.microsoft.com...
> What are you trying to achieve?
> Look at the sp_addlinkedserver system procedure.
> "Thou shall lear to type. Clicking is the work of the Devil."
> -- undisclosed apostle
> ;)
>
> ML
> --
> http://milambda.blogspot.com/

from nvarchar to ntext

I would like to change my field type from nvarchar to ntext.
Is it possible? Are there any restriction for ntext?
I have stored procedues and views using the filed.
Are there any impact for this changes?
Any information is great appreciated,Hi
Depending on what you are doing, you may need to change to code to use
WRITETEXT and UPDATETEXT See books online for more information about using
these functions.
You can change the column using the ALTER TABLE ... ALTER COLUMN...
statement.
John
"Souris" <Souris@.discussions.microsoft.com> wrote in message
news:CFD8641B-7177-4D62-B608-9BA21A75F64F@.microsoft.com...
>I would like to change my field type from nvarchar to ntext.
> Is it possible? Are there any restriction for ntext?
> I have stored procedues and views using the filed.
> Are there any impact for this changes?
> Any information is great appreciated,
>

From DB to FileSystem

Hello All,

I have a file stored in the DB, I am converting the document to another type (this already works) but when a conversion is taking place I need the file to go from the DB to the FileSystem (where the conversion takes place).

I have: response.OutputStream.Write

But instead of outputting to the user I want to save it to a sepcifed location.

Thanks in advance,

AdrianDo you mean that you want to save it on the user's machine, or the Web server? You can't do the former, so hopefully you mean the latter.

It depends a little on how you're conferting it, but you should be able to use the File or FileStream classes to do what you want.

Give us more details about what you need to do, and we can be more specific.

Don|||Right my company has developed a system that stores documents in the SQL db. I have created a plug-in for this tool so that when someone uploads a powerpoint document it converts it into an automatic on-line slideshow. Some people already have uploaded work they want converted so I am working on a converter.

So I need to grab the file from the SQLDB, store it in a spcified location and run the convert script (already works).

Does explain what I need it for, so basically, a user clicks on a button, it passed the FileID, I want it to move the file from the SQL server onto a specified folder on the file system where the PPT converter can do it's thing.

Thanks in advance,

Adrian|||Hi Adrian,

Okay, gotcha. The general way to do it is to read the data from the db, which gives you a stream of bytes. Create a FileStream object and write the data to the stream. When you close the stream, the file will be on your drive.

The .NET framework docs for FileStream have a pretty good example of what you need to do.

Don|||OK i'll try and find them, thanks for your help.

Adrian|||I'm having troubles finding the example you suggested, any ideas?

A huge thank you,

Adrian|||Ok I think i've found something, I have a script (see below) that creates the file, now I want to get the file from the database and stream it into the file. I tried this:

dr.Read()

' Make a new FileStream object, exposing our data file.
' If the file exists, open it, and if it doesn't, then Create it.
Dim fs As FileStream = New FileStream("C:\thisisatestppt.ppt", FileMode.CreateNew)

' create the reader and writer, based on our file stream
Dim w As BinaryWriter = New BinaryWriter(fs)
Dim r As BinaryReader = New BinaryReader(fs)

Try
Dim b As Boolean = Convert.ToBoolean(dr("Content"))
w.Write(b)
Catch
End Try

fs.Close()

dr.Close()

But this fails beacuse I think the dr("Content") needs putting into a stream...

Thanks,

Adrian|||Hi Adrian,

First, what is r for? You're not using it, and I don't see any reason to.

One problem is that you're not closing the BinaryWriter. Add this line right before you close the filestream:

w.Close()

I think you may be closing the filestream before you write anything to it.

Don|||yep, sorted it now. I have created the same sort of thing but it creates a buffer and loops through the file until it has completed writing the buffer.

Thanks for you help!

Adrian

Frequent errors in Application Event Viewer

Hi all,
I encounter many of this errors in Application event viewer regarding
SQL server 2005:
Type: Error
Date: 07/03/2008
Time: 8.54.24
Event: 19019
Source: MSSQL$INSTNEW
Category: Failover
User: N/A
Computer: NEW
Description:
[sqsrvres] CheckQueryProcessorAlive: sqlexecdirect failed
Type: Error
Date: 07/03/2008
Time: 8.54.24
Event: 19019
Source: MSSQL$INSTNEW
Category: Failover
User: N/A
Computer: NEW
Description:
[sqsrvres] printODBCError: sqlstate = 08S01; native error = 2746;
message = [Microsoft][SQL Native Client]TCP Provider: An existing
connection was
forcibly closed by the remote host.
Type: Error
Date: 07/03/2008
Time: 8.54.24
Event: 19019
Source: MSSQL$INSTNEW
Category: Failover
User: N/A
Computer: NEW
Description:
[sqsrvres] printODBCError: sqlstate = 08S01; native error = 2746;
message = [Microsoft][SQL Native Client]Communication link failure
Type: Error
Date: 07/03/2008
Time: 8.54.24
Event: 19019
Source: MSSQL$INSTNEW
Category: Failover
User: N/A
Computer: NEW
Description:
[sqsrvres] OnlineThread: QP is not online.
They occours every time in this order. 6-7 times a day.
I've searched but individually, they seem to be generics errors.
Could you help me?
Thanks in advance
TonyThat is the cluster service checking to see if the SQL Server is responsive.
Get enough of those in a row, and cluster serv tries to restart or failover
the sql group
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Tony_da_Roma" <killerloop@.post.com> wrote in message
news:ff950762-ed63-4c69-9e5e-f45a5386b2e4@.n58g2000hsf.googlegroups.com...
> Hi all,
> I encounter many of this errors in Application event viewer regarding
> SQL server 2005:
> Type: Error
> Date: 07/03/2008
> Time: 8.54.24
> Event: 19019
> Source: MSSQL$INSTNEW
> Category: Failover
> User: N/A
> Computer: NEW
> Description:
> [sqsrvres] CheckQueryProcessorAlive: sqlexecdirect failed
>
> Type: Error
> Date: 07/03/2008
> Time: 8.54.24
> Event: 19019
> Source: MSSQL$INSTNEW
> Category: Failover
> User: N/A
> Computer: NEW
> Description:
> [sqsrvres] printODBCError: sqlstate = 08S01; native error = 2746;
> message = [Microsoft][SQL Native Client]TCP Provider: An existing
> connection was
> forcibly closed by the remote host.
>
> Type: Error
> Date: 07/03/2008
> Time: 8.54.24
> Event: 19019
> Source: MSSQL$INSTNEW
> Category: Failover
> User: N/A
> Computer: NEW
> Description:
> [sqsrvres] printODBCError: sqlstate = 08S01; native error = 2746;
> message = [Microsoft][SQL Native Client]Communication link failure
>
> Type: Error
> Date: 07/03/2008
> Time: 8.54.24
> Event: 19019
> Source: MSSQL$INSTNEW
> Category: Failover
> User: N/A
> Computer: NEW
> Description:
> [sqsrvres] OnlineThread: QP is not online.
> They occours every time in this order. 6-7 times a day.
> I've searched but individually, they seem to be generics errors.
> Could you help me?
> Thanks in advance
> Tony

FREETEXT Help please

Hi All... I'm trying to play around with the "FREETEXT" function to see if it might help us with our application. I have a column of type "text" and I understand it needs to have the Full-text specification set to yes. But I cant seem to figure out how to do that. Can anyone help me out with this please?

Thanks! -- Curt

FreeText is not a function, it is a predicate used for FullText index searches, if you cannot find Full Text the questions is what version and edition of SQL Server are you using. In 2000 it is a separate install and it is not avalable in Express by default you have to use code to implement it. All other editions of 2005 you can enable it either from the menu or right click in Management Studio. Here are all the Full Text predicates CONTAINS, CONTAINSTABLE, FREETEXT and FREETEXTTBALE, run a search for all in the BOL(books online). Hope this helps.

Free Form Typing

I have a report where I need to be able to free type in information? I would need it to pull the data that I have added to the report as well as give me blank spaces to free form?
does this make sense?Have you tried using Parameter fields?

Freakin X-files type unexplained bull crap...

People
Scenario 1
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. It has a column POSTALCODE. This table has no indexes/keys yet. The database size as per SQL Server enterprise manager is 77MB. I create a clustered index on the POSTALCODE column. The database size now is 135MB and space available is 24MB. I shrink the database. The database size is now back to 77MB. YIKES! Does the clustered index take no space'?
Scenario 2
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. It has a column POSTALCODE. This table has no indexes/keys yet. The database size as per SQL Server enterprise manager is 77MB. I create a clustered index on the POSTALCODE column. The database size now is 135MB and space available is 24MB.
I run sp_spaceused @.updateusage=true. The database size is now 135MB and the space available is 74MB. Want more? I shrink the database and lo behold everything's intact!!!!!!!!!!!!! What the f...
Can anyone, I mean ANYONE, including all you MVPs explain this sort of a typical MS crap
Thanks.This is a multi-part message in MIME format.
--=_NextPart_000_0095_01C3F0B0.98CB5A80
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
No surprises here. In order to create a clustered index on a table, you
need 1.2X the size of the table's data in free space available. This
equates to 1.2*77MB = 92.4MB - in addition to the existing 77MB. Once the
clustered index has been created, it frees up that space again. Thus, your
database will have a fair bit of unused space when you are done.
Check out Kalen Delaney's "Inside SQl Server 2000" for more details.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Frustrated..." <anonymous@.discussions.microsoft.com> wrote in message
news:746F0D84-3ABF-4E75-9FF4-56434676608C@.microsoft.com...
People,
Scenario 1:
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. It
has a column POSTALCODE. This table has no indexes/keys yet. The database
size as per SQL Server enterprise manager is 77MB. I create a clustered
index on the POSTALCODE column. The database size now is 135MB and space
available is 24MB. I shrink the database. The database size is now back to
77MB. YIKES! Does the clustered index take no space'
Scenario 2:
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. It
has a column POSTALCODE. This table has no indexes/keys yet. The database
size as per SQL Server enterprise manager is 77MB. I create a clustered
index on the POSTALCODE column. The database size now is 135MB and space
available is 24MB.
I run sp_spaceused @.updateusage=true. The database size is now 135MB and the
space available is 74MB. Want more? I shrink the database and lo behold
everything's intact!!!!!!!!!!!!! What the f...?
Can anyone, I mean ANYONE, including all you MVPs explain this sort of a
typical MS crap?
Thanks.
--=_NextPart_000_0095_01C3F0B0.98CB5A80
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

No surprises here. In order to =create a clustered index on a table, you need 1.2X the size of the table's data =in free space available. This equates to 1.2*77MB =3D 92.4MB - in addition =to the existing 77MB. Once the clustered index has been created, it frees =up that space again. Thus, your database will have a fair bit of unused =space when you are done.
Check out Kalen Delaney's "Inside SQl =Server 2000" for more details.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Frustrated..." wrote in message news:746=F0D84-3ABF-4E75-9FF4-56434676608C@.microsoft.com...People,Scenario 1:I have a small DEMOGRAPHICS table that has close to 500000 rows of =data. It has a column POSTALCODE. This table has no indexes/keys yet. The =database size as per SQL Server enterprise manager is 77MB. I create a clustered =index on the POSTALCODE column. The database size now is 135MB and space =available is 24MB. I shrink the database. The database size is now back to 77MB. =YIKES! Does the clustered index take no space'Scenario 2:I have a =small DEMOGRAPHICS table that has close to 500000 rows of data. It has a =column POSTALCODE. This table has no indexes/keys yet. The database size as per =SQL Server enterprise manager is 77MB. I create a clustered index on the =POSTALCODE column. The database size now is 135MB and space available is 24MB. =I run sp_spaceused @.updateusage=3Dtrue. The database size is now 135MB and =the space available is 74MB. Want more? I shrink the database and lo behold =everything's intact!!!!!!!!!!!!! What the f...?Can anyone, I mean ANYONE, =including all you MVPs explain this sort of a typical MS crap?Thanks.

---

--=_NextPart_000_0095_01C3F0B0.98CB5A80--|||Don't forget that any non-clustered indexes are also de-allocated, but that
space is not freed until the overall transaction is complete, so that the
existing indexes can be snapped back in if need be.
If you're worried about this space usage, you could always use
SORT_IN_TEMPDB, althought that'll use more space, just not in your local
database. (and you could have TempDB on a different set of spindles).
James Hokes
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23%23eTGqN8DHA.2764@.TK2MSFTNGP09.phx.gbl...
No surprises here. In order to create a clustered index on a table, you
need 1.2X the size of the table's data in free space available. This
equates to 1.2*77MB = 92.4MB - in addition to the existing 77MB. Once the
clustered index has been created, it frees up that space again. Thus, your
database will have a fair bit of unused space when you are done.
Check out Kalen Delaney's "Inside SQl Server 2000" for more details.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Frustrated..." <anonymous@.discussions.microsoft.com> wrote in message
news:746F0D84-3ABF-4E75-9FF4-56434676608C@.microsoft.com...
People,
Scenario 1:
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. It
has a column POSTALCODE. This table has no indexes/keys yet. The database
size as per SQL Server enterprise manager is 77MB. I create a clustered
index on the POSTALCODE column. The database size now is 135MB and space
available is 24MB. I shrink the database. The database size is now back to
77MB. YIKES! Does the clustered index take no space'
Scenario 2:
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. It
has a column POSTALCODE. This table has no indexes/keys yet. The database
size as per SQL Server enterprise manager is 77MB. I create a clustered
index on the POSTALCODE column. The database size now is 135MB and space
available is 24MB.
I run sp_spaceused @.updateusage=true. The database size is now 135MB and the
space available is 74MB. Want more? I shrink the database and lo behold
everything's intact!!!!!!!!!!!!! What the f...?
Can anyone, I mean ANYONE, including all you MVPs explain this sort of a
typical MS crap?
Thanks.|||Thanks. If you read carefully, you will see that I am NOT surprised at the fact that a clustered index took space. Quite the contrary actually.
-- Tom Moreau wrote: --
No surprises here. In order to create a clustered index on a table, yo
need 1.2X the size of the table's data in free space available. Thi
equates to 1.2*77MB = 92.4MB - in addition to the existing 77MB. Once th
clustered index has been created, it frees up that space again. Thus, you
database will have a fair bit of unused space when you are done
Check out Kalen Delaney's "Inside SQl Server 2000" for more details
-
To
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDB
SQL Server MV
Columnist, SQL Server Professiona
Toronto, ON Canad
www.pinnaclepublishing.com/sq
"Frustrated..." <anonymous@.discussions.microsoft.com> wrote in messag
news:746F0D84-3ABF-4E75-9FF4-56434676608C@.microsoft.com..
People
Scenario 1
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. I
has a column POSTALCODE. This table has no indexes/keys yet. The databas
size as per SQL Server enterprise manager is 77MB. I create a clustere
index on the POSTALCODE column. The database size now is 135MB and spac
available is 24MB. I shrink the database. The database size is now back t
77MB. YIKES! Does the clustered index take no space'?
Scenario 2
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. I
has a column POSTALCODE. This table has no indexes/keys yet. The databas
size as per SQL Server enterprise manager is 77MB. I create a clustere
index on the POSTALCODE column. The database size now is 135MB and spac
available is 24MB
I run sp_spaceused @.updateusage=true. The database size is now 135MB and th
space available is 74MB. Want more? I shrink the database and lo behol
everything's intact!!!!!!!!!!!!! What the f...
Can anyone, I mean ANYONE, including all you MVPs explain this sort of
typical MS crap
Thanks|||This is a multi-part message in MIME format.
--=_NextPart_000_0104_01C3F0B4.E5AA0410
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
Then I guess I don't understand your problem. The growth of the database is
in reaction to having to create a clustered index. It frees the space - but
does not shrink the database - when the index build is complete.
As for the use of @.updateusage, a better test would be to update the usage
before and after the clustered index build, in order to get accurate
information. It appears from your experience that Enterprise Manager did
not update usage before querying the space used.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Frustrated..." <anonymous@.discussions.microsoft.com> wrote in message
news:8418A72E-DBE0-41A2-AE13-8B13B41C3E1A@.microsoft.com...
Thanks. If you read carefully, you will see that I am NOT surprised at the
fact that a clustered index took space. Quite the contrary actually.
-- Tom Moreau wrote: --
No surprises here. In order to create a clustered index on a table,
you
need 1.2X the size of the table's data in free space available. This
equates to 1.2*77MB = 92.4MB - in addition to the existing 77MB. Once
the
clustered index has been created, it frees up that space again. Thus,
your
database will have a fair bit of unused space when you are done.
Check out Kalen Delaney's "Inside SQl Server 2000" for more details.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Frustrated..." <anonymous@.discussions.microsoft.com> wrote in message
news:746F0D84-3ABF-4E75-9FF4-56434676608C@.microsoft.com...
People,
Scenario 1:
I have a small DEMOGRAPHICS table that has close to 500000 rows of
data. It
has a column POSTALCODE. This table has no indexes/keys yet. The
database
size as per SQL Server enterprise manager is 77MB. I create a clustered
index on the POSTALCODE column. The database size now is 135MB and
space
available is 24MB. I shrink the database. The database size is now back
to
77MB. YIKES! Does the clustered index take no space'
Scenario 2:
I have a small DEMOGRAPHICS table that has close to 500000 rows of
data. It
has a column POSTALCODE. This table has no indexes/keys yet. The
database
size as per SQL Server enterprise manager is 77MB. I create a clustered
index on the POSTALCODE column. The database size now is 135MB and
space
available is 24MB.
I run sp_spaceused @.updateusage=true. The database size is now 135MB
and the
space available is 74MB. Want more? I shrink the database and lo behold
everything's intact!!!!!!!!!!!!! What the f...?
Can anyone, I mean ANYONE, including all you MVPs explain this sort of
a
typical MS crap?
Thanks
--=_NextPart_000_0104_01C3F0B4.E5AA0410
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Then I guess I don't understand your problem. The growth of the database is in reaction to having to =create a clustered index. It frees the space - but does not shrink the =database - when the index build is complete.
As for the use of @.updateusage, a =better test would be to update the usage before and after the clustered index build, =in order to get accurate information. It appears from your experience =that Enterprise Manager did not update usage before querying the space used.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Frustrated..." wrote in message news:841=8A72E-DBE0-41A2-AE13-8B13B41C3E1A@.microsoft.com...Thanks. If you read carefully, you will see that I am NOT surprised at the fact =that a clustered index took space. Quite the contrary actually. -- Tom =Moreau wrote: -- No =surprises here. In order to create a clustered index on a table, you need 1.2X the size of the table's data =in free space available. This equates to =1.2*77MB =3D 92.4MB - in addition to the existing 77MB. Once the clustered index has been created, it =frees up that space again. Thus, your database =will have a fair bit of unused space when you are =done. Check out Kalen Delaney's "Inside SQl =Server 2000" for more details. = -- Tom --- = Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL =Server MVP Columnist, SQL Server Professional Toronto, ON Canada http://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql ="Frustrated..." wrote in message news:746=F0D84-3ABF-4E75-9FF4-56434676608C@.microsoft.com... &nb=sp; People, =Scenario 1: I have a small DEMOGRAPHICS table that =has close to 500000 rows of data. It has a column =POSTALCODE. This table has no indexes/keys yet. The =database size as per SQL Server enterprise manager is 77MB. I create a clustered index on the POSTALCODE column. =The database size now is 135MB and space =available is 24MB. I shrink the database. The database size is now back to 77MB. YIKES! Does the clustered index =take no space' =Scenario 2: I have a small DEMOGRAPHICS table that =has close to 500000 rows of data. It has a column =POSTALCODE. This table has no indexes/keys yet. The =database size as per SQL Server enterprise manager is 77MB. I create a clustered index on the POSTALCODE column. =The database size now is 135MB and space =available is 24MB. I run sp_spaceused @.updateusage=3Dtrue. The database size is now 135MB and the space available is 74MB. Want more? I =shrink the database and lo behold everything's intact!!!!!!!!!!!!! What the f...? Can anyone, I mean ANYONE, including all =you MVPs explain this sort of a typical MS crap? Thanks

---

--=_NextPart_000_0104_01C3F0B4.E5AA0410--|||Your frustration stems from a fundamental lack of understanding... A table
that took up 77MB with no indexes on it would still take up 77MB once a
clustered index is applied because a clustered index is the same thing as an
Index Organized Table in Oracle (if you are familiar with Oracle).
Basically the leaf level of the clustered index IS the table. This is the
reason you can have only one per table. Obviously, there would be a few
extra pages for the top and middle layers of the index, but in most cases
the amount of space that they occupy are negligible.
Now, if you create non-clustered indexes those will definitely use extra
space...
"Frustrated..." <anonymous@.discussions.microsoft.com> wrote in message
news:8418A72E-DBE0-41A2-AE13-8B13B41C3E1A@.microsoft.com...
> Thanks. If you read carefully, you will see that I am NOT surprised at the
fact that a clustered index took space. Quite the contrary actually.
> -- Tom Moreau wrote: --
> No surprises here. In order to create a clustered index on a table,
you
> need 1.2X the size of the table's data in free space available. This
> equates to 1.2*77MB = 92.4MB - in addition to the existing 77MB.
Once the
> clustered index has been created, it frees up that space again.
Thus, your
> database will have a fair bit of unused space when you are done.
> Check out Kalen Delaney's "Inside SQl Server 2000" for more details.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "Frustrated..." <anonymous@.discussions.microsoft.com> wrote in
message
> news:746F0D84-3ABF-4E75-9FF4-56434676608C@.microsoft.com...
> People,
> Scenario 1:
> I have a small DEMOGRAPHICS table that has close to 500000 rows of
data. It
> has a column POSTALCODE. This table has no indexes/keys yet. The
database
> size as per SQL Server enterprise manager is 77MB. I create a
clustered
> index on the POSTALCODE column. The database size now is 135MB and
space
> available is 24MB. I shrink the database. The database size is now
back to
> 77MB. YIKES! Does the clustered index take no space'
> Scenario 2:
> I have a small DEMOGRAPHICS table that has close to 500000 rows of
data. It
> has a column POSTALCODE. This table has no indexes/keys yet. The
database
> size as per SQL Server enterprise manager is 77MB. I create a
clustered
> index on the POSTALCODE column. The database size now is 135MB and
space
> available is 24MB.
> I run sp_spaceused @.updateusage=true. The database size is now 135MB
and the
> space available is 74MB. Want more? I shrink the database and lo
behold
> everything's intact!!!!!!!!!!!!! What the f...?
> Can anyone, I mean ANYONE, including all you MVPs explain this sort
of a
> typical MS crap?
> Thanks|||This is a multi-part message in MIME format.
--=_NextPart_000_0027_01C3F0C9.D41032F0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
One other reason for the apparent discrepancy is that the un-indexed table
could have been heavily fragmented. Adding a clustered index automatically
defrags the table.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Don Peterson" <no1@.nunya.com> wrote in message
news:ejwVbtO8DHA.1040@.TK2MSFTNGP10.phx.gbl...
Your frustration stems from a fundamental lack of understanding... A table
that took up 77MB with no indexes on it would still take up 77MB once a
clustered index is applied because a clustered index is the same thing as an
Index Organized Table in Oracle (if you are familiar with Oracle).
Basically the leaf level of the clustered index IS the table. This is the
reason you can have only one per table. Obviously, there would be a few
extra pages for the top and middle layers of the index, but in most cases
the amount of space that they occupy are negligible.
Now, if you create non-clustered indexes those will definitely use extra
space...
"Frustrated..." <anonymous@.discussions.microsoft.com> wrote in message
news:8418A72E-DBE0-41A2-AE13-8B13B41C3E1A@.microsoft.com...
> Thanks. If you read carefully, you will see that I am NOT surprised at the
fact that a clustered index took space. Quite the contrary actually.
> -- Tom Moreau wrote: --
> No surprises here. In order to create a clustered index on a table,
you
> need 1.2X the size of the table's data in free space available. This
> equates to 1.2*77MB = 92.4MB - in addition to the existing 77MB.
Once the
> clustered index has been created, it frees up that space again.
Thus, your
> database will have a fair bit of unused space when you are done.
> Check out Kalen Delaney's "Inside SQl Server 2000" for more details.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "Frustrated..." <anonymous@.discussions.microsoft.com> wrote in
message
> news:746F0D84-3ABF-4E75-9FF4-56434676608C@.microsoft.com...
> People,
> Scenario 1:
> I have a small DEMOGRAPHICS table that has close to 500000 rows of
data. It
> has a column POSTALCODE. This table has no indexes/keys yet. The
database
> size as per SQL Server enterprise manager is 77MB. I create a
clustered
> index on the POSTALCODE column. The database size now is 135MB and
space
> available is 24MB. I shrink the database. The database size is now
back to
> 77MB. YIKES! Does the clustered index take no space'
> Scenario 2:
> I have a small DEMOGRAPHICS table that has close to 500000 rows of
data. It
> has a column POSTALCODE. This table has no indexes/keys yet. The
database
> size as per SQL Server enterprise manager is 77MB. I create a
clustered
> index on the POSTALCODE column. The database size now is 135MB and
space
> available is 24MB.
> I run sp_spaceused @.updateusage=true. The database size is now 135MB
and the
> space available is 74MB. Want more? I shrink the database and lo
behold
> everything's intact!!!!!!!!!!!!! What the f...?
> Can anyone, I mean ANYONE, including all you MVPs explain this sort
of a
> typical MS crap?
> Thanks
--=_NextPart_000_0027_01C3F0C9.D41032F0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

One other reason for the apparent =discrepancy is that the un-indexed table could have been heavily fragmented. =Adding a clustered index automatically defrags the table.
-- Tom
----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Don Peterson" wrote in message news:ejwVbtO8DHA.1040=@.TK2MSFTNGP10.phx.gbl...Your frustration stems from a fundamental lack of understanding... A tablethat took up 77MB with no indexes on it would still take up =77MB once aclustered index is applied because a clustered index is the same =thing as anIndex Organized Table in Oracle (if you are familiar with Oracle).Basically the leaf level of the clustered index IS the =table. This is thereason you can have only one per table. Obviously, =there would be a fewextra pages for the top and middle layers of the =index, but in most casesthe amount of space that they occupy are =negligible.Now, if you create non-clustered indexes those will definitely use extraspace..."Frustrated..." wrote in messagenews:841=8A72E-DBE0-41A2-AE13-8B13B41C3E1A@.microsoft.com...> Thanks. If you read carefully, you will see that I am NOT surprised at thefact that a clustered index took space. Quite the contrary actually.>> -- Tom Moreau =wrote: -->> No surprises =here. In order to create a clustered index on a table,you> need 1.2X the size =of the table's data in free space available. This> equates to 1.2*77MB =3D =92.4MB - in addition to the existing 77MB.Once =the> clustered index has been created, it frees up that space again.Thus, =your> database will have a fair bit =of unused space when you are =done.>> Check out Kalen Delaney's "Inside SQl Server 2000" for more details.>> --> Tom>> --->&n=bsp; Thomas A. Moreau, BSc, PhD, MCSE, =MCDBA> SQL Server MVP> Columnist, SQL =Server Professional> Toronto, ON Canada>http://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql>>> "Frustrated..." wrote inmessage> news:746=F0D84-3ABF-4E75-9FF4-56434676608C@.microsoft.com...>  =; People,>> Scenario 1:> I have a small DEMOGRAPHICS =table that has close to 500000 rows ofdata. =It> has a column POSTALCODE. This table has no indexes/keys yet. Thedatabase> size as per SQL =Server enterprise manager is 77MB. I create aclustered> index on the =POSTALCODE column. The database size now is 135MB andspace> available is 24MB. I =shrink the database. The database size is nowback to> 77MB. YIKES! Does the clustered =index take no space'>> =Scenario 2:> I have a small DEMOGRAPHICS =table that has close to 500000 rows ofdata. =It> has a column POSTALCODE. This table has no indexes/keys yet. Thedatabase> size as per SQL =Server enterprise manager is 77MB. I create aclustered> index on the =POSTALCODE column. The database size now is 135MB andspace> available is 24MB.>> I run sp_spaceused @.updateusage=3Dtrue. The database size is now 135MBand the> space available is 74MB. Want =more? I shrink the database and =lobehold> everything's intact!!!!!!!!!!!!! What the f...?>> Can anyone, I mean =ANYONE, including all you MVPs explain this sortof a> typical MS crap?>> =Thanks

---

--=_NextPart_000_0027_01C3F0C9.D41032F0--

Freakin X-files type unexplained bull crap...

People,
Scenario 1:
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. It
has a column POSTALCODE. This table has no indexes/keys yet. The database si
ze as per SQL Server enterprise manager is 77MB. I create a clustered index
on the POSTALCODE column. T
he database size now is 135MB and space available is 24MB. I shrink the data
base. The database size is now back to 77MB. YIKES! Does the clustered index
take no space'
Scenario 2:
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. It
has a column POSTALCODE. This table has no indexes/keys yet. The database si
ze as per SQL Server enterprise manager is 77MB. I create a clustered index
on the POSTALCODE column. T
he database size now is 135MB and space available is 24MB.
I run sp_spaceused @.updateusage=true. The database size is now 135MB and the
space available is 74MB. Want more? I shrink the database and lo behold eve
rything's intact!!!!!!!!!!!!! What the f...?
Can anyone, I mean ANYONE, including all you MVPs explain this sort of a typ
ical MS crap?
Thanks.No surprises here. In order to create a clustered index on a table, you
need 1.2X the size of the table's data in free space available. This
equates to 1.2*77MB = 92.4MB - in addition to the existing 77MB. Once the
clustered index has been created, it frees up that space again. Thus, your
database will have a fair bit of unused space when you are done.
Check out Kalen Delaney's "Inside SQl Server 2000" for more details.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Frustrated..." <anonymous@.discussions.microsoft.com> wrote in message
news:746F0D84-3ABF-4E75-9FF4-56434676608C@.microsoft.com...
People,
Scenario 1:
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. It
has a column POSTALCODE. This table has no indexes/keys yet. The database
size as per SQL Server enterprise manager is 77MB. I create a clustered
index on the POSTALCODE column. The database size now is 135MB and space
available is 24MB. I shrink the database. The database size is now back to
77MB. YIKES! Does the clustered index take no space'
Scenario 2:
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. It
has a column POSTALCODE. This table has no indexes/keys yet. The database
size as per SQL Server enterprise manager is 77MB. I create a clustered
index on the POSTALCODE column. The database size now is 135MB and space
available is 24MB.
I run sp_spaceused @.updateusage=true. The database size is now 135MB and the
space available is 74MB. Want more? I shrink the database and lo behold
everything's intact!!!!!!!!!!!!! What the f...?
Can anyone, I mean ANYONE, including all you MVPs explain this sort of a
typical MS crap?
Thanks.|||Don't forget that any non-clustered indexes are also de-allocated, but that
space is not freed until the overall transaction is complete, so that the
existing indexes can be snapped back in if need be.
If you're worried about this space usage, you could always use
SORT_IN_TEMPDB, althought that'll use more space, just not in your local
database. (and you could have TempDB on a different set of spindles).
James Hokes
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23%23eTGqN8DHA.2764@.TK2MSFTNGP09.phx.gbl...
No surprises here. In order to create a clustered index on a table, you
need 1.2X the size of the table's data in free space available. This
equates to 1.2*77MB = 92.4MB - in addition to the existing 77MB. Once the
clustered index has been created, it frees up that space again. Thus, your
database will have a fair bit of unused space when you are done.
Check out Kalen Delaney's "Inside SQl Server 2000" for more details.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Frustrated..." <anonymous@.discussions.microsoft.com> wrote in message
news:746F0D84-3ABF-4E75-9FF4-56434676608C@.microsoft.com...
People,
Scenario 1:
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. It
has a column POSTALCODE. This table has no indexes/keys yet. The database
size as per SQL Server enterprise manager is 77MB. I create a clustered
index on the POSTALCODE column. The database size now is 135MB and space
available is 24MB. I shrink the database. The database size is now back to
77MB. YIKES! Does the clustered index take no space'
Scenario 2:
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. It
has a column POSTALCODE. This table has no indexes/keys yet. The database
size as per SQL Server enterprise manager is 77MB. I create a clustered
index on the POSTALCODE column. The database size now is 135MB and space
available is 24MB.
I run sp_spaceused @.updateusage=true. The database size is now 135MB and the
space available is 74MB. Want more? I shrink the database and lo behold
everything's intact!!!!!!!!!!!!! What the f...?
Can anyone, I mean ANYONE, including all you MVPs explain this sort of a
typical MS crap?
Thanks.|||Thanks. If you read carefully, you will see that I am NOT surprised at the f
act that a clustered index took space. Quite the contrary actually.
-- Tom Moreau wrote: --
No surprises here. In order to create a clustered index on a table, you
need 1.2X the size of the table's data in free space available. This
equates to 1.2*77MB = 92.4MB - in addition to the existing 77MB. Once the
clustered index has been created, it frees up that space again. Thus, your
database will have a fair bit of unused space when you are done.
Check out Kalen Delaney's "Inside SQl Server 2000" for more details.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Frustrated..." <anonymous@.discussions.microsoft.com> wrote in message
news:746F0D84-3ABF-4E75-9FF4-56434676608C@.microsoft.com...
People,
Scenario 1:
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. It
has a column POSTALCODE. This table has no indexes/keys yet. The database
size as per SQL Server enterprise manager is 77MB. I create a clustered
index on the POSTALCODE column. The database size now is 135MB and space
available is 24MB. I shrink the database. The database size is now back to
77MB. YIKES! Does the clustered index take no space'
Scenario 2:
I have a small DEMOGRAPHICS table that has close to 500000 rows of data. It
has a column POSTALCODE. This table has no indexes/keys yet. The database
size as per SQL Server enterprise manager is 77MB. I create a clustered
index on the POSTALCODE column. The database size now is 135MB and space
available is 24MB.
I run sp_spaceused @.updateusage=true. The database size is now 135MB and the
space available is 74MB. Want more? I shrink the database and lo behold
everything's intact!!!!!!!!!!!!! What the f...?
Can anyone, I mean ANYONE, including all you MVPs explain this sort of a
typical MS crap?
Thanks|||Then I guess I don't understand your problem. The growth of the database is
in reaction to having to create a clustered index. It frees the space - but
does not shrink the database - when the index build is complete.
As for the use of @.updateusage, a better test would be to update the usage
before and after the clustered index build, in order to get accurate
information. It appears from your experience that Enterprise Manager did
not update usage before querying the space used.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Frustrated..." <anonymous@.discussions.microsoft.com> wrote in message
news:8418A72E-DBE0-41A2-AE13-8B13B41C3E1A@.microsoft.com...
Thanks. If you read carefully, you will see that I am NOT surprised at the
fact that a clustered index took space. Quite the contrary actually.
-- Tom Moreau wrote: --
No surprises here. In order to create a clustered index on a table,
you
need 1.2X the size of the table's data in free space available. This
equates to 1.2*77MB = 92.4MB - in addition to the existing 77MB. Once
the
clustered index has been created, it frees up that space again. Thus,
your
database will have a fair bit of unused space when you are done.
Check out Kalen Delaney's "Inside SQl Server 2000" for more details.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Frustrated..." <anonymous@.discussions.microsoft.com> wrote in message
news:746F0D84-3ABF-4E75-9FF4-56434676608C@.microsoft.com...
People,
Scenario 1:
I have a small DEMOGRAPHICS table that has close to 500000 rows of
data. It
has a column POSTALCODE. This table has no indexes/keys yet. The
database
size as per SQL Server enterprise manager is 77MB. I create a clustered
index on the POSTALCODE column. The database size now is 135MB and
space
available is 24MB. I shrink the database. The database size is now back
to
77MB. YIKES! Does the clustered index take no space'
Scenario 2:
I have a small DEMOGRAPHICS table that has close to 500000 rows of
data. It
has a column POSTALCODE. This table has no indexes/keys yet. The
database
size as per SQL Server enterprise manager is 77MB. I create a clustered
index on the POSTALCODE column. The database size now is 135MB and
space
available is 24MB.
I run sp_spaceused @.updateusage=true. The database size is now 135MB
and the
space available is 74MB. Want more? I shrink the database and lo behold
everything's intact!!!!!!!!!!!!! What the f...?
Can anyone, I mean ANYONE, including all you MVPs explain this sort of
a
typical MS crap?
Thanks|||Your frustration stems from a fundamental lack of understanding... A table
that took up 77MB with no indexes on it would still take up 77MB once a
clustered index is applied because a clustered index is the same thing as an
Index Organized Table in Oracle (if you are familiar with Oracle).
Basically the leaf level of the clustered index IS the table. This is the
reason you can have only one per table. Obviously, there would be a few
extra pages for the top and middle layers of the index, but in most cases
the amount of space that they occupy are negligible.
Now, if you create non-clustered indexes those will definitely use extra
space...
"Frustrated..." <anonymous@.discussions.microsoft.com> wrote in message
news:8418A72E-DBE0-41A2-AE13-8B13B41C3E1A@.microsoft.com...
> Thanks. If you read carefully, you will see that I am NOT surprised at the
fact that a clustered index took space. Quite the contrary actually.
> -- Tom Moreau wrote: --
> No surprises here. In order to create a clustered index on a table,
you
> need 1.2X the size of the table's data in free space available. This
> equates to 1.2*77MB = 92.4MB - in addition to the existing 77MB.
Once the
> clustered index has been created, it frees up that space again.
Thus, your
> database will have a fair bit of unused space when you are done.
> Check out Kalen Delaney's "Inside SQl Server 2000" for more details.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "Frustrated..." <anonymous@.discussions.microsoft.com> wrote in
message
> news:746F0D84-3ABF-4E75-9FF4-56434676608C@.microsoft.com...
> People,
> Scenario 1:
> I have a small DEMOGRAPHICS table that has close to 500000 rows of
data. It
> has a column POSTALCODE. This table has no indexes/keys yet. The
database
> size as per SQL Server enterprise manager is 77MB. I create a
clustered
> index on the POSTALCODE column. The database size now is 135MB and
space
> available is 24MB. I shrink the database. The database size is now
back to
> 77MB. YIKES! Does the clustered index take no space'
> Scenario 2:
> I have a small DEMOGRAPHICS table that has close to 500000 rows of
data. It
> has a column POSTALCODE. This table has no indexes/keys yet. The
database
> size as per SQL Server enterprise manager is 77MB. I create a
clustered
> index on the POSTALCODE column. The database size now is 135MB and
space
> available is 24MB.
> I run sp_spaceused @.updateusage=true. The database size is now 135MB
and the
> space available is 74MB. Want more? I shrink the database and lo
behold
> everything's intact!!!!!!!!!!!!! What the f...?
> Can anyone, I mean ANYONE, including all you MVPs explain this sort
of a
> typical MS crap?
> Thanks|||One other reason for the apparent discrepancy is that the un-indexed table
could have been heavily fragmented. Adding a clustered index automatically
defrags the table.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Don Peterson" <no1@.nunya.com> wrote in message
news:ejwVbtO8DHA.1040@.TK2MSFTNGP10.phx.gbl...
Your frustration stems from a fundamental lack of understanding... A table
that took up 77MB with no indexes on it would still take up 77MB once a
clustered index is applied because a clustered index is the same thing as an
Index Organized Table in Oracle (if you are familiar with Oracle).
Basically the leaf level of the clustered index IS the table. This is the
reason you can have only one per table. Obviously, there would be a few
extra pages for the top and middle layers of the index, but in most cases
the amount of space that they occupy are negligible.
Now, if you create non-clustered indexes those will definitely use extra
space...
"Frustrated..." <anonymous@.discussions.microsoft.com> wrote in message
news:8418A72E-DBE0-41A2-AE13-8B13B41C3E1A@.microsoft.com...
> Thanks. If you read carefully, you will see that I am NOT surprised at the
fact that a clustered index took space. Quite the contrary actually.
> -- Tom Moreau wrote: --
> No surprises here. In order to create a clustered index on a table,
you
> need 1.2X the size of the table's data in free space available. This
> equates to 1.2*77MB = 92.4MB - in addition to the existing 77MB.
Once the
> clustered index has been created, it frees up that space again.
Thus, your
> database will have a fair bit of unused space when you are done.
> Check out Kalen Delaney's "Inside SQl Server 2000" for more details.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "Frustrated..." <anonymous@.discussions.microsoft.com> wrote in
message
> news:746F0D84-3ABF-4E75-9FF4-56434676608C@.microsoft.com...
> People,
> Scenario 1:
> I have a small DEMOGRAPHICS table that has close to 500000 rows of
data. It
> has a column POSTALCODE. This table has no indexes/keys yet. The
database
> size as per SQL Server enterprise manager is 77MB. I create a
clustered
> index on the POSTALCODE column. The database size now is 135MB and
space
> available is 24MB. I shrink the database. The database size is now
back to
> 77MB. YIKES! Does the clustered index take no space'
> Scenario 2:
> I have a small DEMOGRAPHICS table that has close to 500000 rows of
data. It
> has a column POSTALCODE. This table has no indexes/keys yet. The
database
> size as per SQL Server enterprise manager is 77MB. I create a
clustered
> index on the POSTALCODE column. The database size now is 135MB and
space
> available is 24MB.
> I run sp_spaceused @.updateusage=true. The database size is now 135MB
and the
> space available is 74MB. Want more? I shrink the database and lo
behold
> everything's intact!!!!!!!!!!!!! What the f...?
> Can anyone, I mean ANYONE, including all you MVPs explain this sort
of a
> typical MS crap?
> Thanks