Should Float or Decimal data type be used for dollar amounts? The answer is easy. Never floats. NEVER! Floats were according to IEEE 754 always binary, only the new standard IEEE 754R defined decimal formats. Many of the fractional binary parts can never equal the exact decimal representation. Any binary number can be written as … Read more
The spt_values table is not mentioned in the the SQL Server documentation but it goes back to the Sybase days and there is some extremely minimal documentation in the Sybase online docs that can be summed up in this comment: To see how it is used, execute sp_helptext and look at the text for one … Read more
No problem doing this. Schemas really have no impact when establishing foreign key relationships between tables. Just make sure the appropriate people have the permissions necessary for the schemas you intend to use.
Nulls are negatively viewed from the perspective of database normalization. The idea being that if a value can be nothing, then you really should split that out into another sparse table such that you don’t require rows for items which have no value. It’s an effort to make sure all data is valid and valued. … Read more
The technical definition of an identifying relationship is that a child’s foreign key is part of its primary key. CREATE TABLE AuthoredBook ( author_id INT NOT NULL, book_id INT NOT NULL, PRIMARY KEY (author_id, book_id), FOREIGN KEY (author_id) REFERENCES Authors(author_id), FOREIGN KEY (book_id) REFERENCES Books(book_id) ); See? book_id is a foreign key, but it’s also … Read more
These are some of the Maximum Capacity Specifications for SQL Server 2008 R2 Database size: 524,272 terabytes Databases per instance of SQL Server: 32,767 Filegroups per database: 32,767 Files per database: 32,767 File size (data): 16 terabytes File size (log): 2 terabytes Rows per table: Limited by available storage Tables per database: Limited by number … Read more
Solution: make index a string (because strings, in essence, have infinite “arbitrary precision”). Or if you use an int, increment index by 100 instead of 1. The performance problem is this: there is no “in between” values between two sorted items. item index —————– gizmo 1 <<—— Oh no! no room between 1 and 2. … Read more
“Column Tetris” Actually, you can do something, but this needs deeper understanding. The keyword is alignment padding. Every data type has specific alignment requirements. You can minimize space lost to padding between columns by ordering them favorably. The following (extreme) example would waste a lot of physical disk space: CREATE TABLE t ( e int2 … Read more
You could have a design like: +—————+ +——————-+ | PRODUCTS |—–< PRODUCT_VARIANTS | +—————+ +——————-+ | #product_id | | #product_id | | product_name | | #variant_id | +—————+ | sku_id | | +——————-+ | | +——–^——–+ +——–^——–+ | PRODUCT_OPTIONS |—–< VARIANT_VALUES | +—————–+ +—————–+ | #product_id | | #product_id | | #option_id | | #variant_id … Read more