No Image

Naming conventions for sql

August 10, 2019 jlion 0

I have some coworkers that haven’t worked a lot with sql subqueries. Subqueries are a great tool, but can be confusing for folks who have limited sql experience. It’s my opinion that, like any code, good naming, proper formatting and …

No Image

SQL Partitioned Tables

March 25, 2019 jlion 0

I’ve been looking into table partitioning as a way to manage large historical tables. The idea is to be able to keep the size of the table reasonably static as new rows are inserted by removing older rows. My goal …

No Image

Making a user DBO for all databases on a server

March 19, 2019 jlion 0

Recently I had a need to assign a user DBO to all databases on a server.

I put this script together, it seems to do the trick.

declare @login varchar(255)='testuser'

declare @databasename varchar(255),
        @sql varchar(max)

declare xcn cursor fast_forward local 

Sql Script Mover

January 13, 2017 jlion 0

I’ve just posted an updated version of my SQL Script Mover utility on codeplex, here: https://sqlscriptmove.codeplex.com

This is a utility for extracting schema from a sql server database. It supports views, stored procedures, functions, tables, triggers and DDL triggers.

 …

Faster Stored Procedures

January 2, 2017 jlion 0

Here are some thoughts on optimizing stored the performance of stored procedures.

In no particular order:

Clustered Indexes

Make sure your tables have clustered indexes.

It’s amazing to me that folks create tables without clustered indexes. A clustered index is …

No Image

LINQ vs SQL

July 20, 2016 jlion 0

The excellent Joseph Albahari, author of LINQpad, wrote a blog post: Why LINQ beats SQL and while I really like LINQpad (how else would I debug LINQ?)  I do think Joseph misses the point a little with SQL. He …

No Image

How to generate random data

June 9, 2016 jlion 0

I’ve been doing some performance testing of sql recently and wanted lots of data to work with. I didn’t want real data, with names and other confidential information. I’ve been using a function I created a few years ago and …

No Image

Linq to Entities vs the Database

April 28, 2016 jlion 0

I’ve been working with sql server for a long time. I like sql, I like the immediate feedback that I get from working in SQL Management Studio, and because I do have a lot of experience I can quickly construct …