Reputation
1,819
Top tag
Next privilege 2,000 Rep.
Edit questions and answers
Badges
8 24
Newest
 Yearling
Impact
~72k people reached

10h
awarded  Yearling
May
15
revised What causes “attribute key cannot be found when processing” errors?
Forgot to update the title from a previous question I ended up not asking
May
15
asked What causes “attribute key cannot be found when processing” errors?
May
13
comment (MySQL) user table and profile separate, or same table?
Interesting, @RickJames. My understanding in MS SQL is that such columns are always stored out-of-row, so vertical partitioning isn't useful, but it seems that MySQL is a little more complicated.
May
12
answered (MySQL) user table and profile separate, or same table?
May
12
comment What is a cursor?
I am surprised to see that this basic question doesn't seem to have been asked on DBA or SO yet! This SO answer does a good job of reviewing the reasons to use a cursor (and some of the caveats; they really should be a... next-to-last resort).
May
11
answered Pagination performance, with subquery, inner join and where
May
10
comment Index on Column with only 3 status?
What's the schema for this table? Are the three values roughly equal in frequency? Have you tried adding an index and checking your execution plans?
May
6
comment How does unique key help to improve SQL query performance?
+1, with a small note. In your first example with a unique index, the seek isn't quite the only thing necessary: because there are other fields in the query, the database must also do a key lookup to retrieve the record, after finding it in the unique index. Unless the unique index is also the table's clustering index, in which case the data will be stored with the index.
May
4
comment Why SELECT is so slow
What version of MySQL are you using? My understanding is that early versions would use only one index per table, per query, so being unable to scan the w1 and w2 keys and merge those, it would instead have to do a table scan. How long does it take to SELECT w2 FROM words WHERE w2 = 3112, referencing only a single indexed column?
Apr
29
comment Identity seed increased when using IDENTITY_INSERT
It's normal for different versions of a database to have different behavior (though hopefully it's well-documented!), so having your unit tests aware of this is absolutely normal. You seem to have a preference against it, not a strong reason. Are you sure you need to solve this problem?
Apr
27
comment T-SQL insert value based on identity column
@PaulWhite: Are you sure? On my SQL 2014 machine, I get "...NOT NULL constraints require that computed columns be persisted." when trying to add a PK to a computed column. I believe it does need to be PERSISTED NOT NULL (though MS SQL will make it NOT NULL automatically if you mark it as a PK).
Apr
27
answered Schema Design with “Seasonal” Data
Apr
24
comment Slow queries on billions-rows-table // index used
Without clustered tables, my next recommendation for range queries would be partitioning.
Apr
23
answered What are valid usage scenarios for HEAP tables?
Apr
21
answered Datawarehouse - Dimension, Fact, or Degenerative dimension
Apr
20
answered New fact table or new dimension
Apr
17
answered Is it good or bad practice to have a fact table relate to the same dimension table multiple times?
Apr
17
answered Should I create a Factless Fact Table or Denormalise my table
Apr
16
comment When should I use a unique constraint instead of a unique index?
On MS SQL, they're implemented the same. Try creating two tables with the same data, one with a unique constraint, the other with a unique index. They will use the same amount of index space, and both will be able to seek against the unique index which (in practice) is created either way.