table size and performance

I have a table with 38 columns
I now need to add another 9 columns or more
or move them to a separate table

The table has millions of rows and is accessed quite a lot (like every 30 secs by 200 users), so i'm thinking by adding more columns to this table will really impact performance, but i'm not sure
can someone help clarify is this is a big NO NO or whether it will be fine adding these on same table

How urgent is that?
You can always script the add columns command and schedule it to run it during the night so tomorrow when you return the new columns are already there and the users won't suffer with the impact.

Approach.... Do this when the impact is very less.
1. Create a NEW Table with all fields ( old + new )
2. Move data from old table to new table.
3. Rename the old table to some other table. Here you have consider the dependencies to other objects for time being.
4. Rename the new table to old table.

You should be fine. But what you should be looking into is Indexing after you add the columns. I have had tables with 300 columns and millions of rows. Try getting list of all the queries users or process run and based on that add indexes. Do not just keep adding indexes but use your best judgement.

If you feel space is going to be an issue and you may have NULL values in some of the columns you should look into sparse columns. You could go upto 10,000 columns on a table, and if the data is NULL it uses 0 bytes untill filled.

It depends. How many bytes are in the row now? How many bytes will the new columns add per row?

For example, adding 9 bit columns would have almost no impact on the performance of the table. Adding 9 varchar(8000) columns could destroy the performance.

You could add the columns initially by using a separate table to store them, keyed exactly the same as the original table. Then you could:
1) rename the original table
2) create a view with the same name as the original table that combines the data from both tables.

As to performance, the critical thing for best overall performance is to have the best clustered index on the table. Determining what that is will require reviewing index stats. But now would be a good time to do that, since you need to make some rather big changes to the table anyway.

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

tblCommonTrackingData is the key table, obviously. I did spend some time analyzing this, as it's more complex than I typically see. I recommend changing the indexes as coded below.

This table is most commonly accessed via "vpkDeviceID" [as shown in lines 9-26 of the missing index stats (sheet 1), and line 3 of the index usage stats (sheet 2)].

Therefore, I would cluster the table first on "vpkDeviceID". [Hopefully that column's numeric and not char/varchar, but either way, it should lead the key.] Based on index stats, "dGPSDateTime" should be next in the key. And, since SQL so strongly prefer unique clus keys, I added"[ipkCommanTrackingID" as the last key column. Yes, it's a composite clustering key, but that's not the problem some people try to claim, since your actual lookups use those columns to lookup, and your other nonclus indexes would have those columns in them anyway. [Yes, generally clustering keys should be "narrow, ever-increasing, blah blah blah" but this is a specific case. Being able to accurately go from general to specific is what makes an expert an expert :-)]

If data compression is available on your instance, uncomment it, as it improves performance. If it's not available, naturally remove references to it from the code.

I'll be back with recommendations for tblGroupUser_Details later.

/* Drop all existing indexes before creating new clustered index.*/
/* Needed indexes will be recreated laster.*/
DROP INDEX IX_tblCommonTrackingData_dGpsDateTime ON tblCommonTrackingData;
DROP INDEX IX_tblCommonTrackingData_Id_DeviceID_GpsDate ON tblCommonTrackingData;
/* The PK constraint must be dropped in order to change the clustering on the table.*/
/* The PK constraint will be recreated later, but as nonclustered.*/
ALTER TABLE tblCommonTrackingData DROP CONSTRAINT PK__tblCommo__0851714D5411D660;

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days.
Lets' understand the Au…

Introduction
In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…