It’s not very often that I need to setup database mirroring and because it’s something I do rarely, I can never remember the exact syntax. In the code below ServerP is the Principle, ServerM the mirror and ServerW the witness. — On ServerP CREATE ENDPOINT [Mirror] AUTHORIZATION [domain\sqladmin] STATE=STARTED AS TCP (LISTENER_PORT=7777, LISTENER_IP=ALL) FOR DATA_MIRRORING ( [...]
T-SQL
SQL 2012 – Windowed Functions
I recently installed a copy of SQL2012 RC0 and wanted to look at the new language features that have been added. In particular the improvements to the OVER clause caught my attention as I could think of a scenario where this would be useful. Back in March of this year I created a blog entry called [...]
Dynamic Forms in ASP.NET
As part of an ASP.NET application I recently created I wanted to add a capability that allowed power users to define set of questions that could be used by the application. In other words, when the application needed changing to allow additional information to be stored, the users can make the changes themselves using a few [...]
SQL Filestream with ASP.NET
I inherited an asp.net application that stores documents as binary large objects in SQL Server. I wanted to change the application to use SQL Filestream when storing and retrieving documents. I didn’t want to make major changes to the application and so used the following method to implement FileStream with the least effort. Before using FileStream [...]
Inline views with CTE’s
Every now and then I need to write a query that joins onto the same tables as in the example below. Here the sales table has two columns representing two different employees and I need to return the name and department of each along with the sales amount and date. SELECT S.SalesAmount, S.SaleDate, E1.Name AS SellingEmployee, [...]
Changing Partitions with Split and Merge
I try to create my partitions so that each will contain roughly the same amount of rows. After a while there are bound to be variances in the average number of rows in each partition. For example, I have a partition scheme based upon office/location codes. Some offices are busier than others and process a higher number of [...]
Hashes for Updates
I have a data warehouse that receives data from various sources and then the data is copied to other databases/schemas that are better suited for data cubes / ssrs report models. The problem is the amount of data involved is vast and updating one database from another inevitably involves copying a lot of rows that have [...]
Iterative Updates
When writing queries I’m all for using set theory and try avoid cursors and while loops in my code. Occasionally I find that I need to write a query to solve a business problem and at first it appears that I will have to use a cursor or loop to process each row in turn. That [...]
Overriding default collation
Most SQL installations I have worked on have been configured for case insensitivity and while this is fine most of the time, every now and then I need to write a query that requires the case to be considered. DECLARE @A VARCHAR(10)=’Sample’ DECLARE @B VARCHAR(10)=’SAMPLE’ — If you are using an earlier version of — [...]
SQL Merge
The new Merge statement allows you to combine three DML statements into one. In most examples I have seen this is used to update slowly changing dimensions but that doesn’t mean it can’t be useful in other situations. The image below represents a list of products for sale. Every day I receive a file containing an updated [...]
