Tagged Questions
57
votes
10answers
22k views
When/Why to use Cascading in SQL Server?
When setting up foreign keys in SQL Server, under what circumstances should you have it cascade on delete or update, and what is the reasoning behind it?
This probably applies to other databases as ...
52
votes
9answers
4k views
Why are composite primary keys still around?
I'm assigned to migrate a database to a mid-class ERP.
The new system uses composite primary keys here and there, and from a pragmatic point of view, why?
Compared to autogenerated IDs, I can only ...
51
votes
24answers
19k views
Use Float or Decimal for Accounting Application Dollar Amount?
We are rewriting our legacy Accounting System in VB.NET and SQL Server. We brought in a new team of .NET/ SQL Programmers to do the rewrite. Most of the system is already completed with the Dollar ...
44
votes
27answers
7k views
How do you like your primary keys? [closed]
In a fairly animated discussion in my team I was made to think what most people like as primary keys. We had the following groups-
Int/ BigInt which autoincrement are good enough primary keys.
There ...
40
votes
9answers
2k views
When is it better to store flags as a bitmask rather than using an associative table?
I’m working on an application where users have different permissions to use different features (e.g. Read, Create, Download, Print, Approve, etc.). The list of permissions isn’t expected to change ...
38
votes
11answers
3k views
Database-wide unique-yet-simple identifiers in SQL Server
First, I'm aware of this question, and the suggestion (using GUID) doesn't apply in my situation.
I want simple UIDs so that my users can easily communicate this information over the phone :
...
33
votes
6answers
17k views
What's the longest possible worldwide phone number I should consider in SQL varchar(length) for phone
What's the longest possible worldwide phone number I should consider in SQL varchar(length) for phone.
considerations:
+ for country code
() for area code
x + 6 numbers for Extension extension (so ...
32
votes
6answers
1k views
Why database designers do not make IDENTITY columns start from the min value rather than 1?
As we know, In Sql Server, The IDENTITY (n,m) means that the values will start from n, and the increment value is m, but I noticed that all database designers make Identity columns as IDENTITY(1,1) , ...
31
votes
8answers
7k views
How do you effectively model inheritance in a database?
What are the best practices for modeling inheritance in databases?
What are the trade-offs (e.g. queriability)?
(I'm most interested in SQL Server and .NET, but I also want to understand how other ...
24
votes
5answers
6k views
Why & When should I use SPARSE COLUMN? (SQL SERVER 2008)
After going thru some tutorials on SQL SERVER 2008's new feature SPARSE COLUMN, I have found that it doesn't take any space if the column value is 0 or null but when there is a value, it takes 4 times ...
24
votes
8answers
24k views
How to store directory / hierarchy / tree structure in the database?
How do i store a directory / hierarchy / tree structure in the database? Namely MSSQL Server.
@olavk: Doesn't look like you've seen my own answer. The way i use is way better than recursive queries ...
21
votes
13answers
36k views
SQL Server: the maximum number of rows in table
I develop software that stores a lot of data in one of its database tables (SQL Server version 8, 9 or 10). Let's say, about 100,000 records are inserted into that table per day. This is about 36 ...
21
votes
7answers
33k views
Difference between clustered and nonclustered index
Ok, I'm in the middle of developing a project on my own (first time for me for this scale) and it started in a rush so my tables are kind of miserable. I need to add proper index' and need some help.
...
19
votes
12answers
13k views
Best way to store time (hh:mm) in a database
I want to store times in a database table but only need to store the hours and minutes.
I know I could just use DATETIME and ignore the other components of the date, but what's the best way to do this ...
19
votes
9answers
4k views
What are indexes and how can I use them to optimize queries in my database?
I am maintaining a pretty sizable application and database and am noticing some poor database performance in a few of our stored procedures.
I always hear that "adding an index" can be done to help ...
