I just recently hit the 10GB DB limit in SQL Server Express and am looking to drop unnecessary columns, and convert other columns to less heavy data types. I have 29 columns, and just shy of 50M rows. Of that 29, 20 are floats, and only 10 of them are very useful to me. I could totally drop 10 of the float columns and still have all the information I need. Of the 6 remaining columns, 2 are bit, 2 are int (needed for a compound key), and 5 are int but could be tinyint.
How do I make sure that, before I change the data type of those 5 columns to tinyint, things will convert properly? SSMS warns be about potential data loss when doing it from Design, so I want to make sure things are fine before I go ahead and run the Alter Table statement.
Is it as simple as:
SELECT [ExperimentID],[CycleCount] --My compound key
FROM [dbo].[Simulations]
WHERE (parameter1 < 0 OR parameter1 > 255) OR (parameter2 < 0 OR parameter2 > 255) OR (other search conditions)
Or is there a better way to validate these things?
Also, sorry the DB isn't normalized properly, I was recommended not to normalize by a colleague in order to aid later data analysis.
Wherever you want to change type of the column you can CAST-Select it to make sure everything goes ok:
SELECT CAST(Column_Name as tinyint), *
FROM [dbo].[Simulations]
If there are int values it will raise exception.
If you try to shorten strings (like changing varchar(1000) to varchar(100)) there will be message that data will be truncated.