Index_ID of the PK in In-Memory-Tables
-
Comments posted to this topic are about the item Index_ID of the PK in In-Memory-Tables
God is real, unless declared integer.
-
an interesting question, but:
This question is a bit of a mess. It doesn't ask what indexes exist, it asks what a particular query will return, and that query returns two rows not one, and those two rows have two different values in the relevant column. But the answer required is a single value, not two values. So one has to figure out which one is wanted - but the title of the question makes that rather easy.
The last sentence of the explanation is wrong, misleading. A search of sysindexes with WHERE Index_id < 2 will return a row for an in-memory table. Try it for the example in this question, and you will get a row with 0 in the index_id column. Presumablty believing this inaccuracy is why the author thought only one row would be returned to his query.I hope the hash index with 512 buckets on a tinyint column was intended to raise a laugh Yes, it's maybe in the reccomended range, but for a tinyint key we know that the optimal bucket count can not exceed 256.
Tom
-
On which version did you run your test? On SQL 2014 SP2 Dev. the query will only return one row (and I stumbled over a few scripts which failed, since there was no index with ID 0 or 1 for in-memory-tables)
God is real, unless declared integer.
-
Interesting question, thanks
However, I have to agree with Tom
Granted, I did not do much on SQL2014, as we upgraded directly to SQL2016 from SQL2012, so I cannot say for sure what the result would (should) have been in SQL2014.____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex” - t.franz - Monday, May 8, 2017 2:31 AM
SQL 2016 SP1 Dev. But I haven't got CU5 yet, I'll get that and see whether it still does the same.
Tom
-
Query returns 2 rows on SQL 2016 SP1
Plus, the title for the question is misleading. Afaik, there's no guarantee that if there are multiple indexes created on the table, the primary key will get the lowest ID (if it is guaranteed, please post a link to the relevant section of the documentation)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
I have to agree with Tom and Gail about the question. I got it wrong, so I tested it on SQL 2016 SP1 and the query does return 2 rows.
-
my apology for the confusion - I tested it only on SQL 2014 (since I was to lazy to set up a new 2016 server just for testing and did not expect a behavior change). For 2014 my question / answer ist correct:
God is real, unless declared integer.
-
No biggie, Thomas. Thanks for an interesting Monday morning warmup.
-
Updated question to state SQL 2014 .
There are no other indexes created. This is CREATE TABLE DDL, so arguing there might be some other code that was run between the first part of the question and the second is silly. In a question like this, you should assume this is the only code run on the table.
Certainly in SQL 2016, this behaves differently, but the question should work now.
Points awarded back for the misunderstanding.
- TomThomson - Monday, May 8, 2017 4:40 AM
Old age must be destrying my memory; or maybe it's something else. CU5 for SP1 don't exist (yet), I'm already on the latest cu for SP1 (CU2).
The version I'm getting two rows on is Microsoft SQL Server 2016 (SP1-CU2) (KB4013106) - 13.0.4422.0 (X64)Tom
- TomThomson - Tuesday, May 9, 2017 2:02 AM
No worries, Tom. None of us have SQL 2016 SP1 CU5 yet. 😛
-
That was an easy one for me thanks.
“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply