Creating indexes (details required)

I need some idea of how SQLServer 2000 creates indexes, particularly
clustered indexes.
I imagine that creating a clustered index is rather similar to defragmenting
a hard disk. Data has to be read from disparate areas, stored "somewhere"
until there is a large enough area and then written to that area in a
contigious manner.
If that premise is correct, where is the temporary storage area? Is it in
the database itself or is it in the TempDB?
The reason I ask is because SQLServer is crashing my machine when it builds
indexes (for details see my posting "SQLServer crashing server!" in
"microsoft.public.sqlserver.server".
Thanks in advance
Griff
When indexes are created the temporary storage for the duplicate ( if
replacing indexes), etc is on the SAME filegroup as the index will be on..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Griff" <Howling@.The.Moon> wrote in message
news:%232qla4KaEHA.2216@.TK2MSFTNGP10.phx.gbl...
> I need some idea of how SQLServer 2000 creates indexes, particularly
> clustered indexes.
> I imagine that creating a clustered index is rather similar to
defragmenting
> a hard disk. Data has to be read from disparate areas, stored "somewhere"
> until there is a large enough area and then written to that area in a
> contigious manner.
> If that premise is correct, where is the temporary storage area? Is it in
> the database itself or is it in the TempDB?
> The reason I ask is because SQLServer is crashing my machine when it
builds
> indexes (for details see my posting "SQLServer crashing server!" in
> "microsoft.public.sqlserver.server".
> Thanks in advance
> Griff
>

Creating indexes (details required)

I need some idea of how SQLServer 2000 creates indexes, particularly
clustered indexes.
I imagine that creating a clustered index is rather similar to defragmenting
a hard disk. Data has to be read from disparate areas, stored "somewhere"
until there is a large enough area and then written to that area in a
contigious manner.
If that premise is correct, where is the temporary storage area? Is it in
the database itself or is it in the TempDB?
The reason I ask is because SQLServer is crashing my machine when it builds
indexes (for details see my posting "SQLServer crashing server!" in
"microsoft.public.sqlserver.server".
Thanks in advance
GriffWhen indexes are created the temporary storage for the duplicate ( if
replacing indexes), etc is on the SAME filegroup as the index will be on..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Griff" <Howling@.The.Moon> wrote in message
news:%232qla4KaEHA.2216@.TK2MSFTNGP10.phx.gbl...
> I need some idea of how SQLServer 2000 creates indexes, particularly
> clustered indexes.
> I imagine that creating a clustered index is rather similar to
defragmenting
> a hard disk. Data has to be read from disparate areas, stored "somewhere"
> until there is a large enough area and then written to that area in a
> contigious manner.
> If that premise is correct, where is the temporary storage area? Is it in
> the database itself or is it in the TempDB?
> The reason I ask is because SQLServer is crashing my machine when it
builds
> indexes (for details see my posting "SQLServer crashing server!" in
> "microsoft.public.sqlserver.server".
> Thanks in advance
> Griff
>

Creating indexes (details required)

I need some idea of how SQLServer 2000 creates indexes, particularly
clustered indexes.
I imagine that creating a clustered index is rather similar to defragmenting
a hard disk. Data has to be read from disparate areas, stored "somewhere"
until there is a large enough area and then written to that area in a
contigious manner.
If that premise is correct, where is the temporary storage area? Is it in
the database itself or is it in the TempDB?
The reason I ask is because SQLServer is crashing my machine when it builds
indexes (for details see my posting "SQLServer crashing server!" in
"microsoft.public.sqlserver.server".
Thanks in advance
GriffWhen indexes are created the temporary storage for the duplicate ( if
replacing indexes), etc is on the SAME filegroup as the index will be on..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Griff" <Howling@.The.Moon> wrote in message
news:%232qla4KaEHA.2216@.TK2MSFTNGP10.phx.gbl...
> I need some idea of how SQLServer 2000 creates indexes, particularly
> clustered indexes.
> I imagine that creating a clustered index is rather similar to
defragmenting
> a hard disk. Data has to be read from disparate areas, stored "somewhere"
> until there is a large enough area and then written to that area in a
> contigious manner.
> If that premise is correct, where is the temporary storage area? Is it in
> the database itself or is it in the TempDB?
> The reason I ask is because SQLServer is crashing my machine when it
builds
> indexes (for details see my posting "SQLServer crashing server!" in
> "microsoft.public.sqlserver.server".
> Thanks in advance
> Griff
>

Creating DTS Package to archive database from one sql server to another

Hi...

I am new to database programming.

I need to create a DTS Package which should archive a database fron one sql server to another.

I have no idea that which connection objects and which task objects should be used in order to accomplish this task.

Please help me with this.

Any one of your help is greatly appreciated.

Thank youHi There,

Use "Transfer Databases" task. You can find that under Task menu.

Select appropriate options under each tab. Its pretty straight forward.

Test this DTS first using a test database before implementation.

Good Luck!|||I have moved your question from the Articles section of our site to the Forum.

Creating databases under Master

When creating databases is it a good idea to use the master.dbo.sysdatabases database and then create databases under this.

An example I saw for a forum app did this:

IF EXISTS(SELECT name FROMmaster.dbo.sysdatabasesWHERE name= N'EggHeadCafeForum')

DROP DATABASE[EggHeadCafeForum]

GO

CREATE DATABASE[EggHeadCafeForum]ON(NAME= N'EggHeadCafeForum_Data', FILENAME = N'C:\EggHeadCafeForum_Data.MDF' ,SIZE= 5, FILEGROWTH = 10%)LOG ON(NAME= N'EggHeadCafeForum_Log', FILENAME = N'C:\EggHeadCafeForum_Log.LDF' ,SIZE= 1, FILEGROWTH = 10%)

COLLATESQL_Latin1_General_CP1_CI_AS

GO

It would offer one advantage in terms of when having the site hosted all my databases could be moved under the masterdatabase,However I am thinking there may be disadvantages, one being I use theasp.net roles/admin database that isn't created under master

Any feedback would be great.

Cheers

Adam

You cannot create a database "under" master database. All DB's are at the same level. "sysdatabases" is a table located in master db that has a listing of all the databases on the server. So the code "IF EXISTS.." is only checking if the DB already exists by querying against the list and if it does, its dropping it and recreating it.

|||You can connect to master and create the database from there. The database you created however is not UNDER master. There isn't a hierarchy in databases.|||Thanks guys, see what you mean now|||Thanks guys, see what you mean now