Altering Data Length of a Primary Key Column

This is not an article or a script. I am posting this just to share my experience and I feel it can save a lot of time in a specific situation.

Problem – We have a table let’s say Customer and this table has an alpha numeric primary key CustomerID varchar(10). Due to unexpected growth in volume of data we figure out varchar(10) was not sufficient and we decided to make it varchar(50).

When we ran the below script, we were getting error due to foreign key constraint.CustomerID was being referenced in many table maintaining the relations.

ALTER TABLE [dbo].[Garmentlayer]  

ALTER COLUMN LAYERNAME varchar(50) ;

We tried to dropping the constraints , alter the column and then recreate the constraints but it was failing with the following error.

The object 'PKCustomerID' is dependent on column ‘CustomerID’.

Msg 4922, Level 16, State 9, Line 8

ALTER TABLE ALTER COLUMN CustomerID failed because one or more objects access this column.

Again we tried with disabling all the constraints, alter the column and enable the constraints. It failed again. After doing some research we found that it is failing because it is a foreign key to many table and foreign key should be unique and the solution was very simple. Use the Not Null with alter column. Here is the script –

ALTER TABLE [dbo].[Garmentlayer]  

ALTER COLUMN LAYERNAME varchar(50) NOT NULL;

Thanks

SQL Server - Monitoring Table Size

There are many situations when we want to know the size of our database tables or just need a list of largest tables. The simplest way to get these details is use SQL Server Standard Report. Please refer the below screen shots to generate Standard report.This Report provides the fair amount of information and most of them are self explanatory.
SSMS>SelectDataBase>Reports>StandardReports>Disk Usage By Tables/Disk Usage By Top Tables

We are using Azure in our project and our database size is increasing rapidly. Our leadership team want a mechanism to predict the size of database in future, so then can estimate the cost of hosting our application in Azure. Database size is just one of the factor of the cost of cloud hosting.

As a solution we have decided to capture the size of tables every week and later we will be using that information for seeing the pattern of table growth in size. We have created a web job which is running a query and storing the result in a table. Please find the query below (I just found it on internet) and modified it according to our need (I cannot share final query due to corporate policy)-

Begin

 SET NOCOUNT ON
    IF OBJECT_ID('tempdb..#TableSizeMonitoring') IS NOT NULL DROP TABLE #TableSizeMonitoring

Begin

 SET NOCOUNT ON
    IF OBJECT_ID('tempdb..#TableSizeMonitoring') IS NOT NULL DROP TABLE #TableSizeMonitoring

    CREATE TABLE #TableSizeMonitoring
    (
    TableName sysname ,
    [Rows] int ,
    [Reserved] varchar(20),
    [Data] varchar(20),
    [Index_Size] varchar(20),
    [Unused] varchar(20),
    [Reserved_KB] bigint,
    [Data_KB] bigint,
    [Index_Size_KB] bigint,
    [Unused_KB] bigint
    )

    DECLARE @CMD nVarchar(MAX) =''
    SELECT @CMD +='EXEC sp_spaceused ' +  ''''+QUOTENAME(TABLE_SCHEMA)+'.'+ QUOTENAME(TABLE_NAME)+''''+';'+CHAR(10)
    FROM INFORMATION_SCHEMA.TABLES

     INSERT INTO #TableSizeMonitoring (TableName ,[Rows] , [Reserved], [Data] , [Index_Size] , [Unused] )
     EXEC sp_executesql @CMD


     UPDATE #TableSizeMonitoring
     SET [Reserved_KB] = CONVERT(BIGINT,RTRIM(LTRIM(REPLACE([Reserved] , ' KB', '')))),
         [Data_KB] = CONVERT(BIGINT,RTRIM(LTRIM(REPLACE([Data] , ' KB', '')))),
         [Index_Size_KB]= CONVERT(BIGINT,RTRIM(LTRIM(REPLACE([Index_Size] , ' KB', '')))),
         [Unused_KB]= CONVERT(BIGINT,RTRIM(LTRIM(REPLACE([Unused] , ' KB', ''))))


     SELECT TableName, [Rows], Reserved_KB , Data_KB , Index_Size_KB , Unused_KB ,  Data_KB / 1024.0 Data_MB , Data_KB / 1024.0 / 1024.0 Data_GB
     FROM #TableSizeMonitoring
     ORDER BY Data_KB DESC


     DROP TABLE #TableSizeMonitoring
End
--==========================================================
SELECT
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
    sys.tables t
INNER JOIN    
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
WHERE
    t.NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
GROUP BY
    t.Name, s.Name, p.Rows
ORDER BY
    t.Name

I know this is not a complete solution but I feel it can be used as framework for the purpose of monitoring the size of tables over time.

Thanks for reading the article !!!

Have A Great Day Ahead !!!