How to make a column clustered

Here is my create table command

CREATE TABLE EXCLUSIVEITEM(VIEW_ID INTEGER NONCLUSTERED IDENTITY(1,1)PRIMARY KEY,
VIEW_LATEST_REPLY_DATE CLUSTERED INDEX DATETIME)

Is this the way to create one of my columns clustered. The books that I have all use stored procedure I only want to use regular sql commands. Is this possible?Indexes are clustered, columns aren't.

Can you post the assignment as you received it, or give us a URL to it? I'm still not clear on what you're trying to do, so I'm not much help in doing it!

-PatP

How to make (int) Identity field showed like (0001) ?

Hi,

How to make (int) Identity auto increment field showed like

0001

0002

0003

0004

...

Lew:

To perform the formatting you ask about will require a little code and will require converting your IDENTITY column to VARCHAR or NVARCHAR -- something like:

right('000'+convert(varchar(4),anIdentity),4)

For example:

declare @.anIdentityTable table (anIdentity integer identity)
insert into @.anIdentityTable default values

select anIdentity,
right('000'+convert(varchar(4),anIdentity),4) formattedIdentity
from @.anIdentityTable

-- anIdentity formattedIdentity
-- -- --
-- 1 0001

How to know which fields are identity?

Hi. I need to know if a table has identity fields. Is there any view to get
that information?
Regards,
Diego F.SELECT table_schema, table_name, column_name
FROM information_schema.columns
WHERE COLUMNPROPERTY(OBJECT_ID(
QUOTENAME(table_schema)+'.'+QUOTENAME(table_name)),
column_name,'IsIdentity')=1 ;
David Portas
SQL Server MVP
--|||Thanks a lot!
Regards,
Diego F.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> escribi en el
mensaje news:1123150974.908211.221990@.g14g2000cwa.googlegroups.com...
> SELECT table_schema, table_name, column_name
> FROM information_schema.columns
> WHERE COLUMNPROPERTY(OBJECT_ID(
> QUOTENAME(table_schema)+'.'+QUOTENAME(table_name)),
> column_name,'IsIdentity')=1 ;
> --
> David Portas
> SQL Server MVP
> --
>|||I found a problem with that. It doesn't work if I try to use a table from
other database. Is there another possibility?
Regards,
Diego F.
"Diego F." <diegofrNO@.terra.es> escribi en el mensaje
news:OGuMt9NmFHA.3120@.TK2MSFTNGP09.phx.gbl...
> Thanks a lot!
> --
> Regards,
> Diego F.
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> escribi en el
> mensaje news:1123150974.908211.221990@.g14g2000cwa.googlegroups.com...
>|||SELECT
U.name AS table_schema,
T.name AS table_name,
C.name AS column_name
FROM database_name.dbo.syscolumns AS C
JOIN database_name.dbo.sysobjects AS T
ON C.id = T.id
JOIN database_name.dbo.sysusers AS U
ON T.uid = U.uid
WHERE C.status & 0x80 = 0x80 ;
David Portas
SQL Server MVP
--|||Type 'USE AnotherDatabaseName' before that SELECT command.
Or 'AnotherDatabaseName.information_schema.columns' instead of 'information_
schema.columns'.
Diego F. wrote:
> I found a problem with that. It doesn't work if I try to use a table from
> other database. Is there another possibility?
> --
> Regards,
> Diego F.
>
> "Diego F." <diegofrNO@.terra.es> escribi en el mensaje
> news:OGuMt9NmFHA.3120@.TK2MSFTNGP09.phx.gbl...
>
>|||> 'AnotherDatabaseName.informati=ADon_schema.columns' instead of 'informati=
on_schema.columns'.
That gets you the column names but unfortunately the metadata functions
I used are scoped to the current DB so the WHERE clause won't find the
correct columns.
--=20
David Portas=20
SQL Server MVP=20
--|||David Portas wrote:
>
> That gets you the column names but unfortunately the metadata functions
> I used are scoped to the current DB so the WHERE clause won't find the
> correct columns.
Ah, you are right indeed.|||Thank you all. I'll try to deal with that.
Regards,
Diego F.
"Sericinus hunter" <serhunt@.flash.net> escribi en el mensaje
news:VarIe.953$646.265@.newssvr22.news.prodigy.net...
> David Portas wrote:
> Ah, you are right indeed.