Solved

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

Posted on 2014-10-17
6
140 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:ScottPletcher
ScottPletcher 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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:ScottPletcher
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
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…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

744 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now