HLGEM
  • Member for 10 years, 4 months
  • Last seen more than 3 years ago
  • Virginia, United States
Why are constraints applied in the database rather than the code?
20 votes

The data will likely long outlive the application code. If the rule is critical to the data being useful over time (like foreign key constraints that help keep the integrity of the data), it must be ...

View answer
Common Table Expression (CTE) benefits?
14 votes

One place besides recursion where I find CTEs incredibly useful is when creating complex reporting queries. I use a series of CTEs to get chunks of the data I need and then combine in the final select....

View answer
EF Code First uses nvarchar(max) for all strings. Will this hurt query performance?
11 votes

Indexing the biggest concern. From BOL: Columns that are of the large object (LOB) data types ntext, text, varchar(max), nvarchar(max), varbinary(max), xml, or image cannot be specified as key ...

View answer
Is a surrogate key better than a natural key in this case
Accepted answer
11 votes

Email is a particularly bad choice for any PK whether composite or single. See my answer on this question on Stack Overflow for why: https://stackoverflow.com/questions/3804108/is-email-address-a-...

View answer
How could DBAs be more 'programmer friendly'?
9 votes

I think part of the problem is perspective. Dbas and data analysts and database developers have to deal with the data over time. Application developers are concerned with how to make things work when ...

View answer
How can I identify when to create a new table to hold data that can be obtained from a query?
9 votes

One issue not covered in the accepted answer is "do you need this value over time" and "will the formula possibly change". For instance consider the commision example. If the commission is paid, the ...

View answer
Can you give me one example of Business Intelligence?
8 votes

what would a BI person produce that is of value to my organization. I'm going to take a stab at this part of the question as I think others have done a good job of explaining what BI is. I work for a ...

View answer
Is my ER Diagram correct? Why use relationships?
8 votes

It is a very poor practice to expect to maintain the PK/FK relationships from the application. In any db that is nontrivial, data has close to a 100% chance of being changed from other sources ...

View answer
Does the compatibility level for sql server work reliably for scripts?
8 votes

No the compatibility level is to allow deprecated features to work not to prevent you from writing code that won't work on a lower version. If your production machine is SQL Server 2005, you must ...

View answer
How to handle huge table
Accepted answer
7 votes

That is not a large table, SQL-Server should be able to handle that easily assuming you are running on server-grade hardware. If your hardware is severely underpowered then nothing we tell you can ...

View answer
Is it worth to separate columns into multiple tables for one-to-one relational table
6 votes

Another thing to consider in deciding to split the tables or not is the width of the table if you put them all in one table. Many databases will allow you to define a table where the total length of ...

View answer
Potential risks of users connecting to SQL Server, through Excel, as sysadmin?
6 votes

One thing that the financial people should understand is that by giving the Excel the system user, you have bypassed every internal control built into the database or the application. A competent ...

View answer
Good reason to denormalize?
Accepted answer
6 votes

We do something similar with client id. Yes it can improve performance if you don't need to go through all those intervening tables in every query. However, and it's an important however, this is best ...

View answer
What are Objective Business Reasons to Prefer SQL Server 2012 over 2008 R2?
6 votes

When you are buying new the choice is far different than when you are considering upgrading. Buying new it is my belief that you should always buy the newest version you can get. 2008 version will be ...

View answer
Database suggestion for a social network/ knowledge base community?
6 votes

Consider also that there is no reason why you can't use a relational database for some things and the nosql database for other things.

View answer
Column name naming conventions and best practices
6 votes

THe following book talks about using ID as a SQL antipattern and I gree with the author that it is. http://www.amazon.com/SQL-Antipatterns-Programming-Pragmatic-Programmers/dp/1934356557/ref=sr_1_1?s=...

View answer
Is it critical for a database to have a relationship table/diagram or not?
6 votes

You don't need a diagram for it to work, you need one to train the future developers! However, if what you really have is a database with no actual formal PK/FK relationships set up (Which you can do ...

View answer
How does shrinking a SQL Server log file affect performance?
6 votes

OK first yes the log is necessary even with the daily full backups if you want to recovoer in the event of a problem. We backup our transactionlog every 15 minutes. The problem is that you are not ...

View answer
Is it safe to change the name of a database?
5 votes

If you have queries that reference the full database name (which you likely will have if you have cross database queries), they will break. If you have Imports or reports that reference the full ...

View answer
Foreign keys - link using surrogate or natural key?
5 votes

The main reason I support surrogate keys is that natural keys are often subject to change and that means all related tables must be updated which can put quite a load on the server. Further in the ...

View answer
How to avoid using the IN clause in sql query used in a stored procedure
5 votes

Where exists is gnerally faster than IN SELECT userID, Memberid FROM Member INNER JOIN [USER] ON UserMemberID = MemberiD WHERE EXISTS (SELECT *...

View answer
What should every DBA know?
5 votes

First - you must be able to backup and restore a database. You must know how to set up a recurring schedule to backup both the database and the transaction logs. You should know what other ...

View answer
SSIS ETL Question/Option
5 votes

We do this, the environment varaiable is at the user level, so the user for each environment that runs the SQL agent jobs is different. So on the the dev environment our agent user is something like ...

View answer
Performance degradation while updating tables having 10s of millions of records
5 votes

If you follow the very good advice of kgrittn and still have performance issues, you may need to perform the update in batches. You would still perform set-based updates but limit them to the first ...

View answer
Convert varchar column to datetime
4 votes

Likely you have some data that doesn't fit the datetime column somewhere in the file. Insert to a staging table with a varchar or navachar field for the column and look at the data. You may need to ...

View answer
Proper use of NULL, and utilizing CHECK constraints for business logic vs stored procedures
4 votes

I would go for the check constraints, you cannot be positive that records will never get manually entered or adjusted. Data integrity trumps avoiding nulls every single time.

View answer
migrating from SQL server 2005 to 2008
4 votes

Even if you are using the newest version of SSMS, and SQL server 2008, that does not automaically upgrade any databases that you have attached to it. I can see SQL Server 2000 databases in SSMS for ...

View answer
Is there a reason/benefit I am not aware of for this kind setup?
4 votes

I don't know about mysql but in SQL server, you would use this type of thing when you can't afford to have gaps in the sequence from rollbacks. Often this is a requirement for invoice numbering for ...

View answer
Database growing like crazy - what do I do?
4 votes

Something else that can cause ridiculous growth is failing to backup the transaction log frequently. It will grow until it uses up all your space if you don't back it up. This is separate from the ...

View answer
Should you ever not assign an unique ID to each row in a table?
4 votes

Every table should have a Primary key. It could be a single column field that is an int a decimal, a varachr, etc. or it could consist of multiple columns that are unique in combination. It should be ...

View answer