4,026 reputation
11123
bio website sqlblog.com/blogs/…
location Chicago, IL
age
visits member for 2 years, 4 months
seen 11 hours ago

Developer


Mar
27
comment Altering db schema in production environment
We do it all the time. Google up the following article "Developing low maintenance databases", it describes refactoring tables without downtime.
Mar
21
comment Get SSMS to show the time a query completed at?
You can just add one more select at the end of your query: SELECT GetDate()
Mar
4
comment Recommendations for a SQL Server 2014 Database Administrators Course
Almost all questions about IT "quickly become obsolete" - this industry in general is not as static as Mendeleev's Periodic Table or Newton's laws or Euclid's theorems. This " they quickly become obsolete" thing on this particular site does not sound completely right.
Feb
21
awarded  Nice Answer
Feb
13
comment Primary key for link/junction table with time-dependent data
+1 yes, exactly.
Feb
12
comment Postgres database zero down time production deployment
For zero downtime during tables refactoring, we create new tables along with old ones, and switch in small steps: simple-talk.com/sql/database-administration/…
Feb
11
comment Check constraint for Canadian postal code?
+1 Martin, your constraint is readable, but it uses REPLACE to substitute a wide string [ABCEGHJKLMNPRSTVWXYZ] for its short placeholder. Have you benchmarked the performance implications: how faster it is without calling REPLACE twice?
Feb
9
comment How to avoid a race condition with cross db proc calls? and other concerns
Have you considered using a message queue - that could be a better tool for this task.
Feb
5
comment Hierarchical structure with limited number of records per user
@Gufran on preventing cycles: dba.stackexchange.com/questions/14388/…
Feb
5
revised Hierarchical structure with limited number of records per user
added 856 characters in body
Feb
5
answered Hierarchical structure with limited number of records per user
Feb
3
comment Is it OK to keep a value which updates in a table?
Regarding "I have a unique constraint on (a, b) in PostgreSQL, I can have multiple (1, null) values" - in PostgreSql we need to use a unique partial index on (a) where b is null .
Feb
3
comment Skipping some data from Indexing in SQL Server
Only benchmarking can determine your actual impact on the overall performance. On one hand, with a computed column you will filter out non-matches faster. On the other hand, reading matches is slower. Overall, the sum of these two opposite effects depends on your actual data.
Feb
2
comment Is it OK to keep a value which updates in a table?
@ChrisTravers all running totals are always up-to-date, for all historic dates. Constraints guarantee that. So no aggregating is needed for any historic dates. If we have to update some historic row, or insert something back-dated, we update all the later rows' running totals. I think this is much easier in postgreSql, because it has deferred constraints.
Feb
2
comment How is SQL Server returning both a new value and old value during an UPDATE?
@IanBoyd this is why we use constraints to enforce the rules like the one you have problems with. We have replaced the last trigger with constraints more than two years ago, and we are enjoying watertight data integrity ever since. Also we no longer have to learn in great detail locks, isolation levels, etc - constraints just work, as long as you don't use MERGE, of course.
Feb
1
comment How is SQL Server returning both a new value and old value during an UPDATE?
@IanBoyd download the free pdf "Defensive Database programming", chapter 4, lots of examples.
Feb
1
comment Indexing a PK GUID in SQL Server 2012
Regarding "it's horribly slow as it requires a round trip to the DB to create a key" - you can grab as many as you need in one round trip.
Feb
1
revised How to design database for polymorphic relationships
edited tags
Feb
1
comment How is SQL Server returning both a new value and old value during an UPDATE?
READ_COMMITTED_SNAPSHOT is typically causing many more problems with data integrity, rather than solving them. If I understand this scenario correctly, it will help in this case.
Feb
1
comment How is SQL Server returning both a new value and old value during an UPDATE?
When your 3rd tab "Reset the faulty row", that row becomes available. As such, your 2nd tab can allocate it before the 3rd tab marks the earlier row as available. Try making both modifications in on UPDATE in your 3rd tab.