0

I have converted around 395 Excel survey forms to MS SQl database with that many tables. Well, I run a script to assign a primary key on all the tables on my database.First I set the column property to NOT NULL and then i assign the primary key to it. I assign the primary key to a column called 'FPC_Code'. This column contains values such as, _C2348787, _C567489 etc etc......Well, my problem is that THE SECOND I ASSIGN THE PRIMARY KEY TO ALL THE TABLES THEY GET SORTED WRT THE FPC_Code column. I dont want this sorting to take place. I need it to remain unsorted as they are survey forms and would like to preserve the order. I am running out of time and i seriously need help. This is the script I run on all the tables on my database.

ALTERING COLUMN TO NOT NULL

DECLARE Alter_tables_cursor CURSOR
   FOR
   select table_name from information_schema.tables where table_name<>'dtProperties' and table_type<>'VIEW'
OPEN Alter_tables_cursor
DECLARE @tablename sysname
FETCH NEXT FROM Alter_tables_cursor INTO @tablename
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
 PRINT 'Altering  NOT NULL --> '+@tablename+' '
 EXEC('ALTER TABLE '+@tablename+' ALTER COLUMN FPC_CODE varchar(255) NOT NULL')
 FETCH NEXT FROM Alter_tables_cursor INTO @tablename
 
END
PRINT 'All user-defined tables have been Altered.'
DEALLOCATE Alter_tables_cursor

ASSIGNING PK

DECLARE Alter_tables_cursor CURSOR
   FOR
   select table_name from information_schema.tables where table_name<>'dtProperties' and table_type<>'VIEW'
OPEN Alter_tables_cursor
DECLARE @tablename sysname
FETCH NEXT FROM Alter_tables_cursor INTO @tablename
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
 PRINT 'Altering  Primary Key -->'+@tablename+' '
 EXEC('ALTER TABLE '+@tablename+' ADD PRIMARY KEY (FPC_Code)')
 FETCH NEXT FROM Alter_tables_cursor INTO @tablename
 
END
PRINT 'All user-defined tables have been Altered.'
DEALLOCATE Alter_tables_cursor
2
Contributors
1
Reply
2
Views
8 Years
Discussion Span
Last Post by omrsafetyo
0

SQL Server creates primary keys (by default) as clustered - which means it gets sorted.

Create a NONCLUSTERED primary key and you should be all set.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.