CODEWITHSUNDEEP
sql-serverperformanceoptimization
Seibar
Seibar

Reputation: 70253

SQL Server - Does column order matter?

In terms of performance and optimizations:

Upvotes: 29

Views: 11466

Answers (5)

Aidan Ryan
Aidan Ryan

Reputation: 11599

In SQL Server 2005, placement of nullable variable length columns has a space impact - placing nullable variable size columns at the end of the definition can result in less space consumption.

SQL Server 2008 adds the "SPARSE" column feature which negates this difference.

See here.

Upvotes: 9

Nithin
Nithin

Reputation: 1414

Column order does not matter while creating a table. We can arrange the columns while retrieving the data from the database. Primary key can be set to any column or combination of columns.

Upvotes: 0

Daniel Jennings
Daniel Jennings

Reputation: 6480

For the fourth bullet: No you can't specify where you want to add the column. Here is the syntax for ALTER TABLE: https://msdn.microsoft.com/en-us/library/ms190273.aspx

In MySQL they offer an ALTER TABLE ADD ... AFTER ... but this doesn't appear in T-SQL.

If you want to reorder the columns you'll have to rebuild the table.

Edit: For your last last bullet point, you'll have to DROP the table and recreate it to reorder the columns. Some graphical tools that manipulate SQL databases will do this for you and make it look like you're reordering columns, so you might want to look into that.

Upvotes: 3

SQLMenace
SQLMenace

Reputation: 135021

No to the first 3 because the index will hold the data and no the last once also

Upvotes: 1

Ricardo Reyes
Ricardo Reyes

Reputation: 13756

I would say the answer to all those questions is NO, altough my experience with MS-SQL goes as far as SQL2000. Might be a different story in SQL2005

Upvotes: 3

Related Questions