The IGNORE_DUP_KEY option for unique indexes specifies how SQL Server responds to an attempt to INSERT duplicate values: It only applies to tables (not views) and only to inserts. Any insert portion of a MERGE statement ignores any IGNORE_DUP_KEY index setting.
2019-04-05
Far too often, an indexed view is created without consideration for the costs of the indexed view. In this article, Jason Brimhall covers some of the more important costs that are frequently overlooked when considering an indexed view as a performance panacea.
2019-04-05
Kevin Feasel provides three SSMS keyboard shortcuts for the sp_whoisactive calls he uses most often in day-to-day database development and query tuning work.
2019-04-02
The contents of the SELECT, WHERE and ORDER BY clauses are all a matter of determining the story you need to tell. I was asked to look into an issue recently where a 40-core SQL Server instance was fully utilizing a one node over all others...
2019-03-30
It’s surprisingly useful to know the number of active sessions on each of the databases on your servers. With a bit of SQL, we can create a custom metric to track how many sessions recently performed reads or writes on a database.
2019-03-30
I love the questions I get when presenting: Can You Force a Parallel Plan in Query Store. I haven’t a clue. The trick I think is going to be in setting up the test. Let’s try it out.
2019-03-30
Everything can be fixed with a query hint (*cough* directive), right? If a certain process is consistently causing deadlocks, a simple ROWLOCK hint can be added to prevent it, right?
2019-03-30
Previously Gail Shaw looked at using Query Store to compare execution plans, but it’s not the only way that two execution plans can be compared. The other method requires a saved execution plan and the Management Studio execution plan viewer.
2019-03-30
In this webcast, Tim Smith takes a look at various tips and tricks to assist you with performance tuning your SQL Server databases. Knowing how to find and resolve problems is key to improving performance, so join him to learn how to optimize your systems
2018-08-15
4,840 reads
Optimize searching for a string within another string with this string storage and indexing technique.
2018-07-31
5,194 reads
By SQLPals
Why sys.fn_dblog Is Undocumented And Why It's Still There Why sys.fn_dblog...
When I put together the invitation for T-SQL Tuesday #202, I wasn't sure what...
By Steve Jones
My life has some crazy travel stretches for sure. Between speaking, office visits, customer...
Comments posted to this topic are about the item Implementing Type 4 Slowly Changing...
Comments posted to this topic are about the item The Disabled Index
Comments posted to this topic are about the item Server-Level Table sizes
I run this code on SQL Server 2022.
CREATE TABLE Drink
(
drinkid INT NOT NULL
CONSTRAINT DrinkPK PRIMARY KEY CLUSTERED,
drinkname VARCHAR(20),
rating NUMERIC(2, 1)
)
GO
INSERT INTO Drink
(
drinkid,
drinkname,
rating
)
VALUES
(1, 'Margarita', 4.5),
(2, 'Mojito', 4.3),
(3, 'Old Fashioned', 4.7),
(4, 'Martini', 4.4),
(5, 'Cosmopolitan', 4.2)
GO
ALTER INDEX drinkpk ON dbo.drink DISABLE
GO
SELECT * FROM dbo.Drink
GO
What is the result? See possible answers