I mentioned in Conditional INSERT/UPDATE Race Conditionthat most “UPSERT” code is defective and can lead to constraint violations and data integrity issues in a multi-user environment .In this post, I’ll show how to prevent duplicate key errors and data problems with the MERGE statement too.
Read more →
I’ve used sp_detach_db and sp_attach_db to relocate database files for many years.I know that sp_attach_db was deprecated in SQL 2005 but, like most DBAs, I’ve continued to use sp_attach_db mostly out of habit.
Read more →
I developed a custom report for SQL Server Management Studio that wraps the partition details and row counts query I previously posted.Visit the Codeplex project sitefor details.You can download just the released RDL fileor, if you want to customize the report to your liking, download the full project source code.
Read more →
You will likely find the following query useful if you work with partitioned objects.I developed this when I first started using table partitioning in order to verify proper partition boundaries, filegroups and row counts.
Read more →
I posted example scripts to automate RANGE LEFT sliding window maintenance in my last post.As promised, I am sharing a RANGE RIGHT version in this post.
I personally prefer a RANGE RIGHT partition function when partitioning on a data type that includes time.
Read more →
I mentioned in my last post that partitioning provides a great way to manage a time-based sliding window.Unfortunately, there are no out-of-the-box tools to facilitate adjusting the sliding window so I decided to share scripts I’ve developed to automate the task.
Read more →
SQL Server table partitioning provides a great way to manage a time-based sliding window.By mapping each time period to an individual partition, old data can be efficiently purged or archived using a nearly instantaneous switch out of an entire partition.
Read more →
We have a number of de facto standards in our shop, such as always placing data and indexes on separate filegroups. I asked why we bothered with the separate filegroups since the underlying files were often on the same physical disks anyway.
Read more →
After interviewing several candidates for a database position, I was surprised to find that many didn’t know the difference between a block and deadlock.Many used the terms interchangeably because they thought both were synonymous.
Read more →
Performance and concurrency go hand-in-hand.In fact, these are more closely tied that many DBAs and developers realize.I’ll underscore the important relationship between performance and concurrency in this last article of my concurrency series and explain why performance tuning can fix blocking problems in addition to improving response times.
Read more →
In the first post of this series, I reviewed concurrency concepts and highlighted differences in concurrency terminology from a database and application perspective.I’ll now discuss how applications can leverage SQL Server features to meet concurrency objectives.
Read more →
Concurrency is confusing.Terms line “optimistic concurrency control” and “pessimistic concurrency control” can mean quite different things depending on the context.I hope to clarify some points surrounding concurrency as it relates to application and database design with a series of posts.
Read more →
Most of us have long been aware that non-parameterized ad-hoc queries can pollute procedure cache with entries that will never be reused.Each distinct query string is a separate cache entry and, unless the exact query with the same values is executed again, the plan will remain in cache unnecessarily until aged out.
Read more →
Perhaps the single most important SQL Server application development Best Practice is the consistent use of parameters in application code.Parameterized database calls are more secure, easier to program and promote query plan reuse.
Read more →
Security with Ownership Chains
Ownership chaining is one of my favorite SQL Server security features.I like ownership chaining because it allows me to confine data access to specific stored procedures, views and functions while preventing direct access to the underlying tables.
Read more →
I like to keep things simple because simplicity is easier to manage and less prone to error.When I’m faced with schema design decisions, I pick the selection with the least complexity that meets my objectives.
Read more →
Do you know who owns your databases?Execute sp_helpdb on your SQL Server instances and you might find some surprises under the “owner” column.It isn’t uncommon to see accounts of people who have left the company or moved on to other roles in the organization that don’t require privileged database access.
Read more →
The last thing you want to do is introduce instability when gathering useful performance and troubleshooting information via Profiler.However, I’ve found that many DBAs are not aware that Profiler and SQL Trace need to be used carefully in order to minimize overhead that can negatively affect overall SQL Server performance and lead to problems like query timeouts.
Read more →
Conditional INSERT/UPDATE Race Condition
I often see conditional INSERT/UPDATE code like:
CREATEPROCEDURE dbo.Insert_Or_Update_Foo
@ID int,
@Bar int
AS
SETNOCOUNT ON
IFEXISTS(SELECT * FROM dbo.
Read more →
Use Caution with Explicit Transactions in Stored Procedures
Explicit transactions are often used within stored procedures to guarantee all-or-nothing data integrity.However, a little known fact is that a query timeout will leave the transaction open unless non-default session settings and/or special exception handling are used.
Read more →