9,022 reputation
1535
bio website
location
age
visits member for 3 years, 9 months
seen 1 hour ago

2h
awarded  Explainer
12h
answered 1000 visitors per day have my web page and MySQL scratching at certain times and my.cnf
13h
revised rebuild on clustered index, why does datasize shrink?
added 430 characters in body
13h
comment rebuild on clustered index, why does datasize shrink?
@Shanky: That ALTER INDEX statement looks like it was generated by code (as it includes a bunch of options in their default setting) so I suspect that it was built from the existing options of the index. But you are right: if compression wasn't enabled on the clustered index before this was run then that would definitely explain most of the reduction in data footprint. (again: Daniel, could you confirm one way or the other?)
20h
awarded  Nice Answer
1d
revised rebuild on clustered index, why does datasize shrink?
Minor corrections
1d
revised rebuild on clustered index, why does datasize shrink?
Added notes on fill-factor, reindex example, and updates on variable length rows
1d
revised rebuild on clustered index, why does datasize shrink?
Added notes on fill-factor
1d
revised rebuild on clustered index, why does datasize shrink?
added 400 characters in body
1d
answered rebuild on clustered index, why does datasize shrink?
Sep
25
comment Many to Many and Weak Entities
Remember to allow for albums that have no single nominal artist, either by having a dummy artist called "Various" or similar or by making the artist column of the album table nullable. In fact, you could have more than one artist per track, so you might need a many-to-many arrangement there too.
Sep
24
revised MySQL server often randomly disconnected
added 7 characters in body
Sep
24
answered MySQL server often randomly disconnected
Sep
24
comment Enterprise database design
The point is that you should not even be thinking about modelling until you know some detail about what you are modelling. The desired inputs and outputs (and there scale) could dictate overall structure (even general technology used) considerably. The BA work would preferably come first, not be done in parallel.
Sep
24
comment Enterprise database design
That book may help once you have requirements, but you must have a better set of requirements before you can even start. Your client needs some proper business analysis work done to establish what they need the resulting system to achieve (outputs, user stories and processes, inputs). Don't try do this BA work for them (certainly not for free) - it can be very time consuming and is going to need some experience if the client really doesn't currently know what they want in any detail other then "something like this <points>".
Sep
22
comment Alternatives for a LEFT OUTER JOIN query
Do you have a reference for that bug? Someone here experienced with H2 is likely to have a work around if it is a known problem. If the issue is one of performance rather than an error, have you verified that the H2 database has appropriate indexes? Those being missing would explain a massive difference between the two environments.
Sep
22
comment The dump database on a SSD disk is slowly restored in SQL Server
I wouldn't be too surprised: the defaults will no doubt be optimised for local spinning metal or "remote" (iSCSI etc) SAN based storage, not local SSDs where the bandwidth and latency issues are quite different. Given the results seen in sirsql.net/blog/2011/9/26/optimizing-database-restores.html 2048 might be OTT for the buffer count option.
Sep
22
comment What standard should I follow when naming tables and views?
Why would you create views of a table? - as a general action this is pointless, but (depending on your DBMS's security models) in specific cases it can be useful for limiting access to data whereby different users/apps get views that might not reveal everything (hiding either columns, rows, or both) instead of getting direct access to the base table.
Sep
18
comment trigger that will not allow insert if age is less than 18
You should include all the detail you have when reporting a problem. Don't expect us to guess. "i keep getting errors" - what errors? You know the text of the errors but haven't told us. When do you get the errors? When creating the trigger? For all inserts to that table? For updates? Again, you know this so tell us so we don't have to guess. The easier it is for us to answer, the more likely it is you'll get a useful answer instead of us complaining about insufficient information! As an aside, you might find a CHECK CONSTRAINT more efficient and maintainable for this.
Sep
18
answered Does SQL Server handle databases on SMB shares different than on Hard Drives?