4,308 reputation
11627
bio website
location Chicago, IL
age
visits member for 3 years
seen Oct 31 at 20:13

Developer


Aug
11
comment PosgreSQL: very slow select on a single table with no joins
Sending a result set over the network and rendering it on the client takes time. Instead of SELECT * FROM, try running SELECT SUM(LENGTH(id)) FROM - that might run faster.
Jul
25
comment Filling in missing dates in record set from generate_series()
to get more than 1 as count, add more test data.
Jul
25
comment Filling in missing dates in record set from generate_series()
@Mr.Concolato The previous version worked on 9.3. Fixed.
Jul
24
comment Should I shrink my database?
Thank you for the links, but I am not sure they are completely relevant: they mostly assume that the data is going to grow back, such as "Do you ever shrink your data files? I’ve personally never been fond of it, especially for production databases. After all, they’ll simply have to grow again". This is not the case, they will not have to grow again.
Jul
24
comment Should I shrink my database?
Regarding "You really need to find out at what pace it is growing" - I did. "Think about it - what are you going to do with the disk space that you gain from shrinking the database " - I already thought about it. I modified my question accordingly.
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
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
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
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.