Solved

How to alter the existing column data type and make it as primary key ?

Posted on 2014-10-17
6
149 Views
Last Modified: 2014-11-26
I have the following table with more than 2200000 records.

ERID smallint not null    - PK
ESID smallint not null    - PK  
ETID int not null         - PK  
CITY   varchar(25) not null -PK
STATE  VARCHAR(50) not null
ESDETAILS  VARCHAR(50)  null

ERID,ESID ,ETID ,CITY   are my primary keys. I need to change the CITY   column type to Char(30) and also add STATE  also as a primary key. Is it possible to do this changes  without copying existing records to another table ? How ?

ERID smallint not null                 - PK
ESID smallint not null                 - PK  
ETID int not null                          - PK  
CITY   Char(30)  not null              -PK
STATE  VARCHAR(50) not null    -PK
ESDETAILS  VARCHAR(50)  null
0
Comment
Question by:Varshini S
  • 2
  • 2
  • 2
6 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 250 total points
ID: 40386764
>change the CITY   column type to Char(30) and also add STATE  also as a primary key.
Yes is possible, but instead of ALTER it will be DELETE and CREATE, and I recommend against it.

ERID (smallint 2 bytes) + ESID (smallint 2 bytes) + ETID (int 4 bytes) + CITY (char 30 bytes, should really be a varchar) = 38 byte-wide primary key column, and this change would bump that up to 41 to 88 bytes wide.  Performance will greatly suffer.

Any chance you can have a table of states, with the key either char(2) or smallint with a char(2) name of the state, and then use the char(2) (read:  not 50) or smallint column as your key.

Either way.. (change the obvious)
ALTER TABLE your_table
DROP CONSTRAINT the_primary_key_constraint_name

ALTER TABLE your_table
ADD CONSTRAINT fk_name_me PRIMARY KEY (ERID, ESID, ETID, CITY, STATE) 

Open in new window

btw 'more than 2200000' this may take awhile.
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 250 total points
ID: 40387040
Jim has given if you really just want to recreate the PK and not consider anything else.  With a decent disk system, 2.2M rows shouldn't take very long to reindex, esp. if you leave the first three columns of the index the same.  Btw, if you really do want to include city and state in the index, you should encode them, i.e., use a numeric equivalent instead of the char string.


But, before you make that type of PK/index change(s) you should, at a minimum, review/consider all this:

Is the current PK index being used?  Does the table have other indexes?  Does SQL indicate missing index(es) for this table?  In particular, does SQL indicate that city and/or state are being used for key lookups and a corresponding index is missing?

If you want to review index usage for this table overall, I can help do that.
0
 

Author Comment

by:Varshini S
ID: 40387049
Jim Horn ,
You are absolutely correct. Performance will greatly suffer. But I cannot do any major changes in this table now.

Thank you for the solution.  

Is it possible to change the column type CITY   varchar(25) not null -PK    to
 CITY   Char(30)  not null ?
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 65

Expert Comment

by:Jim Horn
ID: 40387093
Yes, although you may have to do that between the DROP and CREATE pk constraint if it's already a part of the primary key.

ALTER TABLE your_table
ALTER COLUMN CITY char(30) NOT NULL
GO

And Scott is correct, lots of other factors in play here.
0
 

Author Comment

by:Varshini S
ID: 40389337
ScottPletcher,

Thank you for your feedback.

I have some questions regarding this.

Btw, if you really do want to include city and state in the index, you should encode them, i.e., use a numeric equivalent instead of the char string.

Question: You are saying I need to create a one more table city with ID column and refer the value in this table. Am I correct ?

Is the current PK index being used?  Does the table have other indexes?  Does SQL indicate missing index(es) for this table?  In particular, does SQL indicate that city and/or state are being used for key lookups and a corresponding index is missing?
Answer: I don't have any other index in this table. But SQL is indicating missing index.

Please let me know how to  review index usage ?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40467506
>> You are saying I need to create a one more table city with ID column and refer the value in this table. Am I correct ? <<

If you need better performance on the lookups, then yes.  An integer will compare faster than a varchar(25 or 30)  string.  But it does take work to set this up.

If the "state" is U.S.A. states, use the 2-char code, not the full state name.  I know Canada has standard abbreviations for their provinces as well.  Not sure about other countries, but it's reasonably they could.


>> But SQL is indicating missing index.
 Please let me know how to  review index usage ? <<

The script below will give you the minimum relevant details on missing indexes and index usage.  By default, the script lists all tables.  Change the @table_name_pattern variable if you want to list only selected table(s).


USE [<your_db_name_here>]

SET DEADLOCK_PRIORITY LOW --probably irrelevant, but just in case

DECLARE @list_missing_indexes bit
DECLARE @table_name_pattern sysname

--NOTE: showing missing indexes can take some time; set to 0 if you don't want to wait.
SET @list_missing_indexes = 1
SET @table_name_pattern = '%' --'%'=all tables.
--SET @table_name_pattern = '%'

PRINT 'Started @ ' + CONVERT(varchar(30), GETDATE(), 120)

IF @list_missing_indexes = 1
BEGIN
    SELECT
        GETDATE() AS capture_date,
        DB_NAME(mid.database_id) AS Db_Name,
        dps.row_count,
        OBJECT_NAME(mid.object_id /*, mid.database_id*/) AS Table_Name,
        mid.equality_columns, mid.inequality_columns, mid.included_columns,
        user_seeks, user_scans, ca1.max_days_active, unique_compiles,
        last_user_seek, last_user_scan, avg_total_user_cost, avg_user_impact,
        system_seeks, system_scans, last_system_seek, last_system_scan, avg_total_system_cost, avg_system_impact,
        mid.statement, mid.object_id, mid.index_handle
    FROM sys.dm_db_missing_index_details mid WITH (NOLOCK)
    CROSS APPLY (
        SELECT DATEDIFF(DAY, create_date, GETDATE()) AS max_days_active FROM sys.databases WHERE name = 'tempdb'
    ) AS ca1
    LEFT OUTER JOIN sys.dm_db_missing_index_groups mig WITH (NOLOCK) ON
        mig.index_handle = mid.index_handle
    LEFT OUTER JOIN sys.dm_db_missing_index_group_stats migs WITH (NOLOCK) ON
        migs.group_handle = mig.index_group_handle
    LEFT OUTER JOIN sys.dm_db_partition_stats dps WITH (NOLOCK) ON
        dps.object_id = mid.object_id AND
        dps.index_id IN (0, 1)
    --order by
        --DB_NAME, Table_Name, equality_columns
    WHERE
        1 = 1
        AND mid.database_id = DB_ID() --only current db
        AND OBJECT_NAME(mid.object_id) LIKE @table_name_pattern
        --AND mid.object_id IN (OBJECT_ID('<table_name_1>'), OBJECT_ID('<table_name_2>'))
    ORDER BY
        --avg_total_user_cost * (user_seeks + user_scans) DESC,
        Db_Name, Table_Name, equality_columns, inequality_columns
END --IF

PRINT 'Midpoint @ ' + CONVERT(varchar(30), GETDATE(), 120)
-- list index usage stats (seeks, scans, etc.)
SELECT
    ius2.row_num, DB_NAME() AS db_name,
    CASE WHEN i.name LIKE ca2.table_name + '%'
         THEN '~' + SUBSTRING(i.name, LEN(ca2.table_name) + 1, 200)
         ELSE i.name END AS index_name,
    CASE WHEN i.is_unique = 0 THEN 'N' ELSE 'Y' END + '.' +
    CASE WHEN i.is_primary_key = 0 AND i.is_unique_constraint = 0 THEN 'N' ELSE 'Y' END AS [uniq?],
    ca2.table_name,
    i.index_id, --ius.user_seeks + ius.user_scans AS total_reads,
    dps.row_count,
    SUBSTRING(key_cols, 3, 8000) AS key_cols, SUBSTRING(nonkey_cols, 3, 8000) AS nonkey_cols,
    ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates,
    ius.last_user_seek, ius.last_user_scan, ius.last_user_lookup, ius.last_user_update,
    fk.Reference_Count AS fk_ref_count,
    DATEDIFF(DAY, CASE WHEN o.create_date > ca1.sql_startup_date THEN o.create_date
        ELSE ca1.sql_startup_date END, GETDATE()) AS max_days_active,
    FILEGROUP_NAME(i.data_space_id) AS filegroup_name,
    ius.system_seeks, ius.system_scans, ius.system_lookups, ius.system_updates,
    ius.last_system_seek, ius.last_system_scan, ius.last_system_lookup, ius.last_system_update
FROM sys.indexes i WITH (NOLOCK)
INNER JOIN sys.objects o WITH (NOLOCK) ON
    o.object_id = i.object_id
CROSS JOIN (
    SELECT create_date AS sql_startup_date FROM sys.databases WHERE name = 'tempdb'
) AS ca1
CROSS APPLY (
    SELECT OBJECT_NAME(i.object_id/*, DB_ID()*/) AS table_name
) AS ca2
OUTER APPLY (
    SELECT
        ', ' + COL_NAME(object_id, ic.column_id)
    FROM sys.index_columns ic
    WHERE
        ic.key_ordinal > 0 AND
        ic.object_id = i.object_id AND
        ic.index_id = i.index_id
    ORDER BY
        ic.key_ordinal
    FOR XML PATH('')
) AS key_cols (key_cols)
OUTER APPLY (
    SELECT
        ', ' + COL_NAME(object_id, ic.column_id)
    FROM sys.index_columns ic
    WHERE
        ic.key_ordinal = 0 AND
        ic.object_id = i.object_id AND
        ic.index_id = i.index_id
    ORDER BY
        COL_NAME(object_id, ic.column_id)
    FOR XML PATH('')
) AS nonkey_cols (nonkey_cols)
LEFT OUTER JOIN sys.dm_db_partition_stats dps WITH (NOLOCK) ON
    dps.object_id = i.object_id AND
    dps.index_id = i.index_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats ius WITH (NOLOCK) ON
    ius.database_id = DB_ID() AND
    ius.object_id = i.object_id AND
    ius.index_id = i.index_id
LEFT OUTER JOIN (
    SELECT
        database_id, object_id, MAX(user_scans) AS user_scans,
        ROW_NUMBER() OVER (ORDER BY MAX(user_scans) DESC) AS row_num --user_scans|user_seeks+user_scans
    FROM sys.dm_db_index_usage_stats WITH (NOLOCK)
    WHERE
        database_id = DB_ID()
        --AND index_id > 0
    GROUP BY
        database_id, object_id
) AS ius2 ON
    ius2.database_id = DB_ID() AND
    ius2.object_id = i.object_id
LEFT OUTER JOIN (
    SELECT
        referenced_object_id, COUNT(*) AS Reference_Count
    FROM sys.foreign_keys
    WHERE
        is_disabled = 0
    GROUP BY
        referenced_object_id
) AS fk ON
    fk.referenced_object_id = i.object_id
WHERE
    i.object_id > 100 AND
    i.is_hypothetical = 0 AND
    i.type IN (0, 1, 2) AND
    o.type NOT IN ( 'IF', 'IT', 'TF', 'TT' ) AND
    (
     o.name LIKE @table_name_pattern AND
     o.name NOT LIKE 'dtprop%' AND
     o.name NOT LIKE 'filestream[_]' AND
     o.name NOT LIKE 'MSpeer%' AND
     o.name NOT LIKE 'MSpub%' AND
     --o.name NOT LIKE 'queue[_]%' AND
     o.name NOT LIKE 'sys%'
    )
    --AND OBJECT_NAME(i.object_id /*, DB_ID()*/) IN ('tbl1', 'tbl2', 'tbl3')
ORDER BY
    --row_count DESC,
    --ius.user_scans DESC,
    --ius2.row_num, --user_scans&|user_seeks
    db_name, table_name,
    -- list clustered index first, if any, then other index(es)
    CASE WHEN i.index_id IN (0, 1) THEN 1 ELSE 2 END,
    key_cols

PRINT 'Ended @ ' + CONVERT(varchar(30), GETDATE(), 120)

SET DEADLOCK_PRIORITY NORMAL
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL server is using more virtual memory. 5 84
Help  needed 3 24
SQL Server 2012 rs - Field1 and Field2 add 1 to Total 4 18
Find results from sql within a time span 11 30
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question