[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • Last Modified:

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

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
Varshini S
Asked:
Varshini S
  • 2
  • 2
  • 2
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
Scott PletcherSenior DBACommented:
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
 
Varshini SAuthor Commented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
Varshini SAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
>> 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now