Does the logical ordering of columns in a table have any impact on their physical order at the storage layer? Yes.
Whether it matters or not is a different issue that I can't answer (yet).
In a similar manner to that described in the frequently linked article from Paul Randal on the anatomy of a record, let's look at a simple two column table with DBCC IND:
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
USE master;
GO
IF DATABASEPROPERTY (N'RowStructure', 'Version') > 0 DROP DATABASE RowStructure;
GO
CREATE DATABASE RowStructure;
GO
USE RowStructure;
GO
CREATE TABLE FixedLengthOrder
(
c1 INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
, c2 CHAR(10) DEFAULT REPLICATE('A', 10) NOT NULL
, c3 CHAR(10) DEFAULT REPLICATE('B', 10) NOT NULL
);
GO
INSERT FixedLengthOrder DEFAULT VALUES;
GO
DBCC IND ('RowStructure', 'FixedLengthOrder', 1);
GO
The output above shows that we need to look at page 89:
DBCC TRACEON (3604);
GO
DBCC PAGE ('RowStructure', 1, 89, 3);
GO
In the output from DBCC PAGE we see c1 stuffed with the character 'A' before c2's 'B':
Memory Dump @0x000000000D25A060
0000000000000000: 10001c00 01000000 41414141 41414141 †........AAAAAAAA
0000000000000010: 41414242 42424242 42424242 030000††††AABBBBBBBBBB...
And just because, lets bust open RowStructure.mdf
with a hex editor and confirm the 'A' string preceeds the 'B' string:
Now repeat the test but reverse the order of the strings, placing the 'B' characters in c1 and the 'A' characters in c2:
CREATE TABLE FixedLengthOrder
(
c1 INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
, c2 CHAR(10) DEFAULT REPLICATE('B', 10) NOT NULL
, c3 CHAR(10) DEFAULT REPLICATE('A', 10) NOT NULL
);
GO
This time our DBCC PAGE output is different and the 'B' string appears first:
Memory Dump @0x000000000FC2A060
0000000000000000: 10001c00 01000000 42424242 42424242 †........BBBBBBBB
0000000000000010: 42424141 41414141 41414141 030000††††BBAAAAAAAAAA...
Again, just for giggles, lets check the hex dump of the data file:
As Anatomy of a Record explains, the fixed and variable length columns of a record are stored in distinct blocks. Logically interleaving fixed and variable column types has no bearing on the physical record. However, within each block the order of your columns does map to the order of bytes in the data file.
CREATE TABLE FixedAndVariableColumns
(
c1 INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
, c2 CHAR(10) DEFAULT REPLICATE('A', 10) NOT NULL
, c3 VARCHAR(10) DEFAULT REPLICATE('B', 10) NOT NULL
, c4 CHAR(10) DEFAULT REPLICATE('C', 10) NOT NULL
, c5 VARCHAR(10) DEFAULT REPLICATE('D', 10) NOT NULL
, c6 CHAR(10) DEFAULT REPLICATE('E', 10) NOT NULL
);
GO
Memory Dump @0x000000000E07C060
0000000000000000: 30002600 01000000 41414141 41414141 †0.&.....AAAAAAAA
0000000000000010: 41414343 43434343 43434343 45454545 †AACCCCCCCCCCEEEE
0000000000000020: 45454545 45450600 00020039 00430042 †EEEEEE.....9.C.B
0000000000000030: 42424242 42424242 42444444 44444444 †BBBBBBBBBDDDDDDD
0000000000000040: 444444†††††††††††††††††††††††††††††††DDD
See also:
Column order doesn’t matter… generally, but – IT DEPENDS!
You're not showing us what you're doing - but based on the table structure, this is what you should do:
create an INSERT
statement that explicitly lists the columns it will insert into - assuming that ID
might be an IDENTITY
column that you don't want / can't insert into
define the exact number of values to fill into these columns
So your INSERT
statement should be something like:
INSERT INTO dbo.tbl_Post (cat_id, ngo_id, title, description, active)
VALUES (42, 4711, 'Some title', 'Some description', 1)
What you should definitely get in the habits of avoiding is using INSERT INTO dbo.tblPost
without explicitly defining the list of column to insert into. This is just a recipe for disaster, as soon as you change your table definition, all your existing INSERT
s will break since they don't match the table definition anymore.
Therefore: always explicitly define the list of columns that an INSERT
statement should fill data into!
Also see Aaron Bertrand's excellent blog post on the topic:
Bad habits to kick: using SELECT * / omit the column list
Best Answer
This is certainly a bug. The fact that the
col1
values happened to be the result of an expression involving random numbers clearly doesn't change what the the correct value forcol2
is supposed to be.DBCC CHECKDB
returns an error if this is run against a permanent table.Gives (for my test run which had one "impossible" row)
It does also report that
And if taken up on the repair option unceremoniously deletes the whole row as it has no way of telling which column is corrupted.
Attaching a debugger shows that the
NEWID()
is being evaluated twice per inserted row. Once before theCASE
expression is evaluated and once inside it.A possible workaround might be to use
Which for one reason or another avoids the issue and only evaluates the expression once per row.