Create table and Index

Hello,
I am using SQL server 2000 with SP4. I am running create table and index as
below
CREATE TABLE dbo.ttcmcs045777 (t_creg CHAR(3) NOT NULL,t_dsca CHAR(30) NOT
NULL,t_Refcntd INTEGER NOT NULL,t_Refcntu INTEGER NOT NULL) ON TEST
CREATE UNIQUE CLUSTERED INDEX Ittcmcs045777_1a ON dbo.ttcmcs045777 (t_creg)
ON TEST1
I am trying to create table in TEST file group and Index in TEST1 file
group. But table and index are both getting created in TEST1 file group. If
I
execute just Creat Table statement alone, then the table is getting created
in TEST file group, but if I execute both statements together, the table an
d
Index is getting created in TEST1.
Any help on this is really appreciated.
Thank you
-mvsWhat happens if you replace it with this?
CREATE TABLE dbo.ttcmcs045777 (t_creg CHAR(3) NOT NULL,t_dsca CHAR(30) NOT
NULL,t_Refcntd INTEGER NOT NULL,t_Refcntu INTEGER NOT NULL) ON TEST
GO
CREATE UNIQUE CLUSTERED INDEX Ittcmcs045777_1a ON dbo.ttcmcs045777 (t_creg)
ON TEST1
http://sqlservercode.blogspot.com/
"mvs" wrote:

> Hello,
> I am using SQL server 2000 with SP4. I am running create table and index a
s
> below
> CREATE TABLE dbo.ttcmcs045777 (t_creg CHAR(3) NOT NULL,t_dsca CHAR(30) NOT
> NULL,t_Refcntd INTEGER NOT NULL,t_Refcntu INTEGER NOT NULL) ON TEST
> CREATE UNIQUE CLUSTERED INDEX Ittcmcs045777_1a ON dbo.ttcmcs045777 (t_creg
)
> ON TEST1
> I am trying to create table in TEST file group and Index in TEST1 file
> group. But table and index are both getting created in TEST1 file group. I
f I
> execute just Creat Table statement alone, then the table is getting create
d
> in TEST file group, but if I execute both statements together, the table
and
> Index is getting created in TEST1.
> Any help on this is really appreciated.
> Thank you
> -mvs
>|||A clustered index *is* the table (the leaf level of the index are the data p
ages). Hence, you cannot
separate a clustered index from the data pages, by definition.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mvs" <mvs@.discussions.microsoft.com> wrote in message
news:0C8E4E4B-C6D1-4294-9A64-D4A4B3F4161D@.microsoft.com...
> Hello,
> I am using SQL server 2000 with SP4. I am running create table and index a
s
> below
> CREATE TABLE dbo.ttcmcs045777 (t_creg CHAR(3) NOT NULL,t_dsca CHAR(30) NOT
> NULL,t_Refcntd INTEGER NOT NULL,t_Refcntu INTEGER NOT NULL) ON TEST
> CREATE UNIQUE CLUSTERED INDEX Ittcmcs045777_1a ON dbo.ttcmcs045777 (t_creg
)
> ON TEST1
> I am trying to create table in TEST file group and Index in TEST1 file
> group. But table and index are both getting created in TEST1 file group. I
f I
> execute just Creat Table statement alone, then the table is getting create
d
> in TEST file group, but if I execute both statements together, the table
and
> Index is getting created in TEST1.
> Any help on this is really appreciated.
> Thank you
> -mvs
>|||Hi,
I tried it, it is same problem.
--
mvs
"SQL" wrote:
[vbcol=seagreen]
> What happens if you replace it with this?
> CREATE TABLE dbo.ttcmcs045777 (t_creg CHAR(3) NOT NULL,t_dsca CHAR(30) NOT
> NULL,t_Refcntd INTEGER NOT NULL,t_Refcntu INTEGER NOT NULL) ON TEST
> GO
> CREATE UNIQUE CLUSTERED INDEX Ittcmcs045777_1a ON dbo.ttcmcs045777 (t_creg
)
> ON TEST1
> http://sqlservercode.blogspot.com/
>
> "mvs" wrote:
>|||My bad I didn't realize it was a clustered index
"mvs" wrote:
[vbcol=seagreen]
> Hi,
> I tried it, it is same problem.
> --
> mvs
>
> "SQL" wrote:
>|||MVS,
Table data by default is stored in a heap structure (unsorted set of data
pages). When you create a clustered index on the table the data is copied
to a contiguous set of pages and physically sorted based on the clustered
index key. Hence you either have a heap structure for a table (INDID = 0
sysindexes) OR a clustered index (INDID = 1 sysindexes) but not both. So
your first statement does create the table ON TEST and your second statement
moves the data, sorts it ON TEST1. So if you want the data to be on TEST
use ON TEST in both statement conversely if you want the data to be on TEST1
use ON TEST! in both statements. That being said your nonclustered indexes
can exist on different filegroup than the clustered index (table).
HTH
Jerry
"mvs" <mvs@.discussions.microsoft.com> wrote in message
news:0C8E4E4B-C6D1-4294-9A64-D4A4B3F4161D@.microsoft.com...
> Hello,
> I am using SQL server 2000 with SP4. I am running create table and index
> as
> below
> CREATE TABLE dbo.ttcmcs045777 (t_creg CHAR(3) NOT NULL,t_dsca CHAR(30) NOT
> NULL,t_Refcntd INTEGER NOT NULL,t_Refcntu INTEGER NOT NULL) ON TEST
> CREATE UNIQUE CLUSTERED INDEX Ittcmcs045777_1a ON dbo.ttcmcs045777
> (t_creg)
> ON TEST1
> I am trying to create table in TEST file group and Index in TEST1 file
> group. But table and index are both getting created in TEST1 file group.
> If I
> execute just Creat Table statement alone, then the table is getting
> created
> in TEST file group, but if I execute both statements together, the table
> and
> Index is getting created in TEST1.
> Any help on this is really appreciated.
> Thank you
> -mvs
>|||Thank you all. Now it really helped me.
--
mvs
"Jerry Spivey" wrote:

> MVS,
> Table data by default is stored in a heap structure (unsorted set of data
> pages). When you create a clustered index on the table the data is copied
> to a contiguous set of pages and physically sorted based on the clustered
> index key. Hence you either have a heap structure for a table (INDID = 0
> sysindexes) OR a clustered index (INDID = 1 sysindexes) but not both. So
> your first statement does create the table ON TEST and your second stateme
nt
> moves the data, sorts it ON TEST1. So if you want the data to be on TEST
> use ON TEST in both statement conversely if you want the data to be on TES
T1
> use ON TEST! in both statements. That being said your nonclustered indexe
s
> can exist on different filegroup than the clustered index (table).
> HTH
> Jerry
> "mvs" <mvs@.discussions.microsoft.com> wrote in message
> news:0C8E4E4B-C6D1-4294-9A64-D4A4B3F4161D@.microsoft.com...
>
>

Create table and Index

Hello,
I am using SQL server 2000 with SP4. I am running create table and index as
below
CREATE TABLE dbo.ttcmcs045777 (t_creg CHAR(3) NOT NULL,t_dsca CHAR(30) NOT
NULL,t_Refcntd INTEGER NOT NULL,t_Refcntu INTEGER NOT NULL) ON TEST
CREATE UNIQUE CLUSTERED INDEX Ittcmcs045777_1a ON dbo.ttcmcs045777 (t_creg)
ON TEST1
I am trying to create table in TEST file group and Index in TEST1 file
group. But table and index are both getting created in TEST1 file group. If I
execute just Creat Table statement alone, then the table is getting created
in TEST file group, but if I execute both statements together, the table and
Index is getting created in TEST1.
Any help on this is really appreciated.
Thank you
-mvs
What happens if you replace it with this?
CREATE TABLE dbo.ttcmcs045777 (t_creg CHAR(3) NOT NULL,t_dsca CHAR(30) NOT
NULL,t_Refcntd INTEGER NOT NULL,t_Refcntu INTEGER NOT NULL) ON TEST
GO
CREATE UNIQUE CLUSTERED INDEX Ittcmcs045777_1a ON dbo.ttcmcs045777 (t_creg)
ON TEST1
http://sqlservercode.blogspot.com/
"mvs" wrote:

> Hello,
> I am using SQL server 2000 with SP4. I am running create table and index as
> below
> CREATE TABLE dbo.ttcmcs045777 (t_creg CHAR(3) NOT NULL,t_dsca CHAR(30) NOT
> NULL,t_Refcntd INTEGER NOT NULL,t_Refcntu INTEGER NOT NULL) ON TEST
> CREATE UNIQUE CLUSTERED INDEX Ittcmcs045777_1a ON dbo.ttcmcs045777 (t_creg)
> ON TEST1
> I am trying to create table in TEST file group and Index in TEST1 file
> group. But table and index are both getting created in TEST1 file group. If I
> execute just Creat Table statement alone, then the table is getting created
> in TEST file group, but if I execute both statements together, the table and
> Index is getting created in TEST1.
> Any help on this is really appreciated.
> Thank you
> -mvs
>
|||A clustered index *is* the table (the leaf level of the index are the data pages). Hence, you cannot
separate a clustered index from the data pages, by definition.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mvs" <mvs@.discussions.microsoft.com> wrote in message
news:0C8E4E4B-C6D1-4294-9A64-D4A4B3F4161D@.microsoft.com...
> Hello,
> I am using SQL server 2000 with SP4. I am running create table and index as
> below
> CREATE TABLE dbo.ttcmcs045777 (t_creg CHAR(3) NOT NULL,t_dsca CHAR(30) NOT
> NULL,t_Refcntd INTEGER NOT NULL,t_Refcntu INTEGER NOT NULL) ON TEST
> CREATE UNIQUE CLUSTERED INDEX Ittcmcs045777_1a ON dbo.ttcmcs045777 (t_creg)
> ON TEST1
> I am trying to create table in TEST file group and Index in TEST1 file
> group. But table and index are both getting created in TEST1 file group. If I
> execute just Creat Table statement alone, then the table is getting created
> in TEST file group, but if I execute both statements together, the table and
> Index is getting created in TEST1.
> Any help on this is really appreciated.
> Thank you
> -mvs
>
|||Hi,
I tried it, it is same problem.
mvs
"SQL" wrote:
[vbcol=seagreen]
> What happens if you replace it with this?
> CREATE TABLE dbo.ttcmcs045777 (t_creg CHAR(3) NOT NULL,t_dsca CHAR(30) NOT
> NULL,t_Refcntd INTEGER NOT NULL,t_Refcntu INTEGER NOT NULL) ON TEST
> GO
> CREATE UNIQUE CLUSTERED INDEX Ittcmcs045777_1a ON dbo.ttcmcs045777 (t_creg)
> ON TEST1
> http://sqlservercode.blogspot.com/
>
> "mvs" wrote:
|||My bad I didn't realize it was a clustered index
"mvs" wrote:
[vbcol=seagreen]
> Hi,
> I tried it, it is same problem.
> --
> mvs
>
> "SQL" wrote:
|||MVS,
Table data by default is stored in a heap structure (unsorted set of data
pages). When you create a clustered index on the table the data is copied
to a contiguous set of pages and physically sorted based on the clustered
index key. Hence you either have a heap structure for a table (INDID = 0
sysindexes) OR a clustered index (INDID = 1 sysindexes) but not both. So
your first statement does create the table ON TEST and your second statement
moves the data, sorts it ON TEST1. So if you want the data to be on TEST
use ON TEST in both statement conversely if you want the data to be on TEST1
use ON TEST! in both statements. That being said your nonclustered indexes
can exist on different filegroup than the clustered index (table).
HTH
Jerry
"mvs" <mvs@.discussions.microsoft.com> wrote in message
news:0C8E4E4B-C6D1-4294-9A64-D4A4B3F4161D@.microsoft.com...
> Hello,
> I am using SQL server 2000 with SP4. I am running create table and index
> as
> below
> CREATE TABLE dbo.ttcmcs045777 (t_creg CHAR(3) NOT NULL,t_dsca CHAR(30) NOT
> NULL,t_Refcntd INTEGER NOT NULL,t_Refcntu INTEGER NOT NULL) ON TEST
> CREATE UNIQUE CLUSTERED INDEX Ittcmcs045777_1a ON dbo.ttcmcs045777
> (t_creg)
> ON TEST1
> I am trying to create table in TEST file group and Index in TEST1 file
> group. But table and index are both getting created in TEST1 file group.
> If I
> execute just Creat Table statement alone, then the table is getting
> created
> in TEST file group, but if I execute both statements together, the table
> and
> Index is getting created in TEST1.
> Any help on this is really appreciated.
> Thank you
> -mvs
>
|||Thank you all. Now it really helped me.
mvs
"Jerry Spivey" wrote:

> MVS,
> Table data by default is stored in a heap structure (unsorted set of data
> pages). When you create a clustered index on the table the data is copied
> to a contiguous set of pages and physically sorted based on the clustered
> index key. Hence you either have a heap structure for a table (INDID = 0
> sysindexes) OR a clustered index (INDID = 1 sysindexes) but not both. So
> your first statement does create the table ON TEST and your second statement
> moves the data, sorts it ON TEST1. So if you want the data to be on TEST
> use ON TEST in both statement conversely if you want the data to be on TEST1
> use ON TEST! in both statements. That being said your nonclustered indexes
> can exist on different filegroup than the clustered index (table).
> HTH
> Jerry
> "mvs" <mvs@.discussions.microsoft.com> wrote in message
> news:0C8E4E4B-C6D1-4294-9A64-D4A4B3F4161D@.microsoft.com...
>
>
sql

Create table + index + primary

for MS SQL 2000
how can I do this in one time (into the CREATE TABLE)

CREATE TABLE [dbo].[Users] (
[id_Users] [int] NOT NULL ,
[Name] [nvarchar] (100) NULL,
[Serial] [nvarchar] (100) NULL,
) ON [PRIMARY]

ALTER TABLE [dbo].[Users] WITH NOCHECK ADD
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[id_Users]
) ON [PRIMARY]

CREATE UNIQUE INDEX [IX_Users] ON [Users]([Serial]) ON [PRIMARY]

and that one

CREATE TABLE [dbo].[UsersExtra] (
[id_Users] [int] NOT NULL
) ON [PRIMARY]

ALTER TABLE [dbo].[UsersExtra] ADD
CONSTRAINT [FK_UsersExtra_Users] FOREIGN KEY
(
[id_Users]
) REFERENCES [Users] (
[id_Users]
) ON DELETE CASCADE

thank youi am getting an error

CREATE TABLE [dbo].[Users] (
[id_Users] [int] NOT NULL PRIMARY KEY clustered,
[Name] [nvarchar] (100) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[UsersExtra] (
[id_UsersExtra] [int] NOT NULL REFERENCES [Users].[id_Users] ON DELETE CASCADE
) ON [PRIMARY]

Msg 1767, Level 16, State 0, Line 50
Foreign key 'FK__Users__id_Co__05D9AC15' references invalid table 'Users.id_Users'.
Msg 1750, Level 16, State 0, Line 50|||you are trying to reference a non-existing table, 'Users.id_Users'|||but I have created it just before

CREATE TABLE [dbo].[Users] (
[id_Users] [int] NOT NULL PRIMARY KEY clustered,
[Name] [nvarchar] (100) NULL
) ON [PRIMARY]|||To get back on the first question ...
You can't create a table and an index in one single statement, except when that index is the PK ... so more than one statement is required to get the job done. You can however combine the create and alter table statements into one. See BOL 'create table'.

Gr,
Yveau|||Add Go and try it again|||but I have created it just beforeyes, you did

however, that is not the source of your problem

you said REFERENCES [Users].[id_Users]

this is invalid syntax, because it is trying to reference a table called "id_Users" belonging to user called "Users"

for the correct syntax, please see the manual

:)

Create New Table

hi i have one question,

i have created a table, let's say CallerList:

CREATE TABLE MyDB.[dbo].[CallerList]
(
[pid] [int] NOT NULL,
[Name] [varchar] NULL,
[Surname] [varchar] NULL,
[Phone] [int] NULL,
[Date] [datetime] NOT NULL

)

now i'd like to create table CallerList1, and i want it to have same names of columns: pid, Name, Surname, Phone, Date, but i don't want to create it in the way as i wrote up, is it possible to somehow COPY these column names from CallerList and not to wirte the whole code again? Like just the names of columns and their properties to have the same...

thanx

Hi,

this should help:

SELECT * INTO CallerList1 FROM CallerList WHERE 0 = 1

So you copy only the structure to the new table CallerList1

--Andreas

Create index on col with data and without data, any time differenc

Hello:
In terms of the time it takes to create an index (non-clustered) in a
column, would it be faster if the column contains only null? Or the time it
takes to create an index will be the same whether this column has data or not.
Thanks,
Q
Hi
I don't think there will be a significant difference in the time taken to
create the index, but if you then significantly change the data in the
column, the index may be fragmented and the statistics out of date, therefore
it would be better to build the index once populated.
John
"Q" wrote:

> Hello:
> In terms of the time it takes to create an index (non-clustered) in a
> column, would it be faster if the column contains only null? Or the time it
> takes to create an index will be the same whether this column has data or not.
> Thanks,
> Q
|||No rows are omitted from an index. Not even rows where the indexed
column is NULL. So the time to index the column would be the same,
regardless of any value (or NULL) (assuming fixed size data type).
Gert-Jan
Q wrote:
> Hello:
> In terms of the time it takes to create an index (non-clustered) in a
> column, would it be faster if the column contains only null? Or the time it
> takes to create an index will be the same whether this column has data or not.
> Thanks,
> Q

Create index on col with data and without data, any time differenc

Hello:
In terms of the time it takes to create an index (non-clustered) in a
column, would it be faster if the column contains only null? Or the time it
takes to create an index will be the same whether this column has data or no
t.
Thanks,
QHi
I don't think there will be a significant difference in the time taken to
create the index, but if you then significantly change the data in the
column, the index may be fragmented and the statistics out of date, therefor
e
it would be better to build the index once populated.
John
"Q" wrote:

> Hello:
> In terms of the time it takes to create an index (non-clustered) in a
> column, would it be faster if the column contains only null? Or the time
it
> takes to create an index will be the same whether this column has data or
not.
> Thanks,
> Q|||No rows are omitted from an index. Not even rows where the indexed
column is NULL. So the time to index the column would be the same,
regardless of any value (or NULL) (assuming fixed size data type).
Gert-Jan
Q wrote:
> Hello:
> In terms of the time it takes to create an index (non-clustered) in a
> column, would it be faster if the column contains only null? Or the time
it
> takes to create an index will be the same whether this column has data or
not.
> Thanks,
> Q

Create index on col with data and without data, any time differenc

Hello:
In terms of the time it takes to create an index (non-clustered) in a
column, would it be faster if the column contains only null? Or the time it
takes to create an index will be the same whether this column has data or not.
Thanks,
QHi
I don't think there will be a significant difference in the time taken to
create the index, but if you then significantly change the data in the
column, the index may be fragmented and the statistics out of date, therefore
it would be better to build the index once populated.
John
"Q" wrote:
> Hello:
> In terms of the time it takes to create an index (non-clustered) in a
> column, would it be faster if the column contains only null? Or the time it
> takes to create an index will be the same whether this column has data or not.
> Thanks,
> Q|||No rows are omitted from an index. Not even rows where the indexed
column is NULL. So the time to index the column would be the same,
regardless of any value (or NULL) (assuming fixed size data type).
Gert-Jan
Q wrote:
> Hello:
> In terms of the time it takes to create an index (non-clustered) in a
> column, would it be faster if the column contains only null? Or the time it
> takes to create an index will be the same whether this column has data or not.
> Thanks,
> Q

create function

I'd like to return a NULL value from a mssql function, but i can't get it
to. here's my sample:
CREATE FUNCTION dbo.FixVarChar (@.val nvarchar(30) )
RETURNS nvarchar(30) AS
BEGIN
declare @.retval as varchar (30)
select @.retval = case when len(@.val)>0 then @.val else null end
return (null)
END
it does NOT return a null value. when I run:
select dbo.fixvarchar('somevalue') as test
it does not return null but what looks like a zero-length string.
i actually need to get the function to return the value (if the string has
len>0) or an actuall NULL value.
help!john wrote on Fri, 9 Jun 2006 10:09:54 -0400:

> I'd like to return a NULL value from a mssql function, but i can't get it
> to. here's my sample:
> CREATE FUNCTION dbo.FixVarChar (@.val nvarchar(30) )
> RETURNS nvarchar(30) AS
> BEGIN
> declare @.retval as varchar (30)
> select @.retval = case when len(@.val)>0 then @.val else null end
> return (null)
> END
> it does NOT return a null value. when I run:
> select dbo.fixvarchar('somevalue') as test
> it does not return null but what looks like a zero-length string.
> i actually need to get the function to return the value (if the string has
>
len>> 0) or an actuall NULL value.
> help!
I've just created your function on my SQL Server 2005 machine, and run the
same select, and get a NULL (when run in Query Analyzer) - and you'll always
get NULL too, unless you fix your last line to be RETURN (@.retval). What
version of SQL Server are you trying this on?
Dan|||Just tried it on SQL Server 2000 too, works fine.
Dan|||it's sql 2000.
I put that last line in there just to ensure i was returning a null value.
(ultimately, the function will return the non-null value if it exists or
null. I have an xml export program that expects null for non-existing
element nodes to be created).
"Daniel Crichton" <msnews@.worldofspack.com> wrote in message
news:uKXChC9iGHA.4344@.TK2MSFTNGP05.phx.gbl...
> Just tried it on SQL Server 2000 too, works fine.
> Dan
>|||I get 'someval' returned on my SQL 2000 SP4 when I replace 'return (null)'
with 'return (@.retval)'. Are you running SP4?
In any case, I see a couple of inconsistencies. You are returning varchar
(30) but the function return data type is nvarchar(30). Also, you are
passing a varchar instead of the nvarchar expected as the function
parameter.
Hope this helps.
Dan Guzman
SQL Server MVP
"john doe" <jdoe@.doe.com> wrote in message
news:%23IjaZ78iGHA.3440@.TK2MSFTNGP02.phx.gbl...
> I'd like to return a NULL value from a mssql function, but i can't get it
> to. here's my sample:
> CREATE FUNCTION dbo.FixVarChar (@.val nvarchar(30) )
> RETURNS nvarchar(30) AS
> BEGIN
> declare @.retval as varchar (30)
> select @.retval = case when len(@.val)>0 then @.val else null end
> return (null)
> END
> it does NOT return a null value. when I run:
> select dbo.fixvarchar('somevalue') as test
> it does not return null but what looks like a zero-length string.
> i actually need to get the function to return the value (if the string has
> len>0) or an actuall NULL value.
> help!
>|||As I said, I tried it on SQL 2000 here, worked fine (returned a null as it
was, returned the value I passed in when I adjusted it to return @.retval,
and a null if the passed in value was a blank string).
Dan
john wrote on Fri, 9 Jun 2006 12:55:24 -0400:
> it's sql 2000.
> I put that last line in there just to ensure i was returning a null value.
> (ultimately, the function will return the non-null value if it exists or
> null. I have an xml export program that expects null for non-existing
> element nodes to be created).
> "Daniel Crichton" <msnews@.worldofspack.com> wrote in message news:uKXChC9i
GHA.4344@.TK2MSFTNGP05.phx.gbl...|||Forgot the ask something else in my reply - how are you testing the return
value? I used query analyser where it shows NULL in the column, but if
you're using something else maybe that is interpreting nulls as empty
strings. Without more information there's not much else I can suggest.
Dan
john wrote on Fri, 9 Jun 2006 12:55:24 -0400:
> it's sql 2000.
> I put that last line in there just to ensure i was returning a null value.
> (ultimately, the function will return the non-null value if it exists or
> null. I have an xml export program that expects null for non-existing
> element nodes to be created).
> "Daniel Crichton" <msnews@.worldofspack.com> wrote in message news:uKXChC9i
GHA.4344@.TK2MSFTNGP05.phx.gbl...