Linked Servers are an easy way to allow direct access between different SQL Servers. But, by using this feature, you may have created a wide-open backdoor into your servers! Read on for more details… Continue reading Are you using Linked Servers? They may be in serious danger!
For this month’s T-SQL Tuesday, Kevin Chant asked us to dream up our fantasy SQL Server feature.
The feature I wish we had is Table Partitioning, but… Better. Unfortunately, the feature by large remained the same way since its release in 2005, without any meaningful improvements (besides some parallelism and statistics maintenance improvements).
But I think Microsoft can do much better. Continue reading #TSQL2sday : I wish SQL Server had better Table Partitioning
Today I’ll talk about the available JOIN operator types in SQL Server (Nested Loops, Hash and Merge Joins), their differences, best practices and complexity. For the samples in this post, we’ll use the free AdventureWorks database sample available here: http://msftdbprodsamples.codeplex.com/releases/view/4004 Introduction: What are Join Operators? A join operator is a type of an algorithm which the SQL Server Optimizer chooses in order to implement logical … Continue reading The LOOP, HASH and MERGE Join Types
Unlike foreign keys, primary keys and unique indexes cannot be “untrusted”. There’s no “NOCHECK” option for unique constraints. The closest there is, is the “ignore_dup_key” option. “Hmm… That sounds interesting”, you must be thinking. But let me stop you right there, buddy. Read this before you go off doing something potentially dangerous… Very, VERY dangerous. But it does NOT mean that you can create such … Continue reading The "IGNORE_DUP_KEY" option in Primary Keys and Unique Indexes
One fateful night, cold and dreary, I’ve stumbled upon how collations can significantly affect performance in SQL Server… Yes, really!
While normalizing a table in their database, one of my clients had weird and inconsistent performance problems and couldn’t find a solution for it for quite a while. Continue reading The Curious Case of… Collations and Performance in SQL Server
CREATE PROCEDURE MoveEmployeesFromManagerToManager @SourceManagerID INT, @DestinationManagerID INT AS DECLARE @RCount INT DECLARE @Output AS TABLE ( Msg NVARCHAR(MAX) ) BEGIN TRY INSERT INTO @Output VALUES( N’Moving employees from ManagerID ‘ + CONVERT(nvarchar(50), @SourceManagerID) + ‘ to ManagerID ‘ + CONVERT(nvarchar(50), @DestinationManagerID) + ‘…’ ) BEGIN TRANSACTION UPDATE Employees SET ManagerID = @DestinationManagerID OUTPUT N’Moved EmployeeID ‘ + CONVERT(nvarchar(50), INSERTED.EmployeeID) INTO @Output FROM HumanResources.Employee AS Employees … Continue reading Using Reporting Services as your Software Frontend
