SQL Server Developer

Query Tuning Minimise the Data Requested Minimising the data queried is a fundamental tenet of performance tuning for any method of data access. In querying a database using SQL, the below query should almost never make it into production : Select * From [Table] Instead, the query should specify the fields to be returned from [...]

Orphan users can occur once you detach databases or restore a database from another SQL Server database instance. The reason for this is that whenever a user is created, a login name and SID will be created. Each database user is mapped with an SID. This SID is different from one SQL Server instance to [...]

Entity Framework originally shipped with two modes – Model First which allowed for the entity model to be created using the designer UI and Database First which created the EF model from a database. EF 4.1 introduced Code-First development, enabling developers to simply generate the EF model and database directly from code. This method rapidly [...]

Entity Framework is fast becoming the default data access method for small to medium sized apps in .NET. Whilst it offers a lot of benefits in terms of productivity and security, working with EF is often very different from working with the standard ADO.NET data-access objects and poses several common issues or ‘gotchas’: Using Different [...]

Have you noticed the ‘guest’ user in all of your databases? Who The Guest User? When you login to the SQL Server, it first verifies you for authentication on the server. If it succeeds, SQL Server checks whether your login is associated or mapped with a database user in the database that the login is [...]

Agile Database development? What’s that? Why would I need that? These were questions I had myself until until recently when I tried it. Several couple of weeks ago, we successfully finished our first sprint and my intention is to pass on this experience and hopefully provoke some reaction from readers. I would love to hear [...]

Extended Events were introduced in SQL Server 2008 as a light-weight monitoring mechanism. Most DBAs typically use SQL Profiler to monitor and analyse SQL Server events. However, SQL Profiler is not a lightweight tool and consumes too many resources for use in a production environment. In SQL Server 2008, Extended Events can only be configured [...]

XQuery was developed by W3C as a query language designed to programmatically interact with and manipulate Extensible Markup Language (XML).  XQuery uses XPath methods and expressions to search and retrieve XML data.  In this article, I’d like to show a few simple, practical examples of these implementations. First, let’s create a table to hold sample [...]

What is A CTE? A CTE is a temporary view that exists only in the scope of running query. A CTE is similar to a view but a CTE is embedded into the query. CTEs can be used in Stored Procedures, User Define Functions (UDFs), Triggers and Views but not in Indexed Views. Below is [...]

Paging Data Paging is a big issue for developers as it is required for many applications but entails many performance problems.  Developers have used different workarounds to support paging. For example, assume we need to display several pages of the HumanResource.Employee object with 5 rows per page. Below is the query you need to execute [...]