RSS

Tag Archives: sql

What is Sharding?

Sharding is a type of database partitioning that is used to separate very large databases the into smaller, faster, more easily managed pieces called data shards.

It’s partitioning that separates very large databases the into smaller, faster, more easily managed parts called data shards. The word shard means a small part of a whole.

 
Leave a comment

Posted by on January 8, 2015 in General

 

Tags: , , , ,

Do Not Use Database Trigger

Or rather, use database trigger carefully.

Trigger is convenient and easy to write, but wrong implementation of database trigger can cause serious performance issue.

Basic rule is to avoid database trigger at all cost, find alternative way to achieve same result. Below is a video from Pluralsight showing usage of a database trigger that performs poorly. The slow performance caused by overhead that comes with database trigger, in this case the operation reads more table that necessary. When alternative approach is used (setting default constraint on a column), the difference is significant.

Try it for yourself.

-- Create our demo
CREATE TABLE [dbo].[charge_demo](
    [charge_no] [dbo].[numeric_id] NOT NULL PRIMARY KEY CLUSTERED,
    [charge_no] [dbo].[numeric_id] NOT NULL,
    [provider_no] [dbo].[numeric_id] NOT NULL,
    [category_no] [dbo].[numeric_id] NOT NULL,
    [charge_dt] [datetime] NOT NULL,
    [charge_amt] [money] NOT NULL,
    [statement_no] [dbo].[numeric_id] NOT NULL,
    [charge_code] [dbo].[status_code] NOT NULL,
    [insert_dt] [datetime] NULL;
GO

-- Create our trigger
CREATE TRIGGER [trg_i_charge_demo]
    ON [dbo].[charge_demo]
    AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    
    UPDATE [dbo].[charge_demo]
    SET [insert_dt] = GETDATE()
    FROM [dbo].[charge_demo] AS [c]
    INNERT JOIN inserted AS [i] ON
        [c].[charge_no] = [i].[charge_no];
END
GO

Source: Hidden Side Effect of Trigger

 
Leave a comment

Posted by on April 19, 2013 in General

 

Tags: , ,

Creating ASP.NET Membership in SQL (Web Forms and MVC)

ASP.NET Membership is Microsoft approach to provide security, authentication and authorization in that regards, for ASP.NET Web Forms and ASP.NET MVC. Both Web Forms and MVC can utilize this feature through Forms Authentication approach.

There are several ways to create Membership database in SQL Server.

SQL Server Registration Tool (Aspnet_regsql.exe) – Wizard

%WINDIR%\Microsoft.Net\Framework\<.net version>\aspnet_regsql.exe

Running this tool will guide you to installing ASP.NET Membership in SQL Server.
See the ASP.NET Membership Tutorial on “Installing the Application Services” section for more instruction.

SQL Server Registration Tool (Aspnet_regsql.exe) – Advanced Options

Beside the wizard guide, you can also use its command prompt with many available options.
See MSDN site for the parameters.

SqlServices.Install Method

To programatically install ASP.NET Membership, use SqlServices.Install method.

SQL Scripts Manual Approach (for Deployment Plan)

Generate sql script with SQL Server Registration Tool (Aspnet_reqsql.exe) to include them in a database project as part of deployment strategy.

%WINDIR%\Microsoft.Net\Framework\<.net version>\aspnet_regsql.exe -sqlexportonly <filename.sql> -A all

SQL Scripts Manual Approach (for Deployment Plan) – Individual Scripts

In the following folder, you will find many .sql script files to install / uninstall ASP.NET Membership. Each service (or feature – Membership, Roles, Profile, Web Parts Personalization, Web Events) is separated by files. This also can be included in a database project as part of deployment strategy.

%WINDIR%\Microsoft.Net\Framework\<.net version>\

ASP.NET Membership Schema Diagram

 
1 Comment

Posted by on July 13, 2012 in General

 

Tags: , , , , , ,

SqlCommand.Execute Methods

There are 4 different Execute methods in SqlCommand class.

ExecuteNonQuery: Executes a Transact-SQL statement against the connection and returns the number of rows affected.

ExecuteReader: Executes the CommandText against the Connection and builds an IDataReader.

ExecuteScalar: Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

ExecuteXmlReader: Sends the CommandText to the Connection and builds an XmlReader object.

Source: MSDN

 
Leave a comment

Posted by on March 5, 2012 in General

 

Tags: , , ,

SQL Union

Union: The UNION operator selects only distinct values by default.

Union All: The UNION ALL operator selects duplicate values.

Source: W3Schools

 
Leave a comment

Posted by on March 5, 2012 in General

 

Tags: ,

Type of SQL Joins

These are type of SQL Joins:

Inner Join (Join): return rows when there is at least one match in both tables.

Left Join: return all rows from the left table, even if there are no matches in the right table.

Right Join: return all rows from the right table, even if there are no matches in the left table.

Full Join: return rows when there is a match in one of the tables.

Cross Join: returns the Cartesian product of rows from tables in the join.

Source: W3Schools, Wikipedia

 
Leave a comment

Posted by on March 5, 2012 in General

 

Tags: ,

SQL Server Execution Plan Definition 101

Heap Table:

A database table that doesn’t have clustered index. As opposed to clustered table.

Clustered Table:

A database table that has clustered index.

Non-clustered and Clustered:

Clustered index: index that stores the actual data.

Non-clustered index: a pointer to the data.

Refer to this post for more detail definition.

Table Scan:

Full scan, that is go and read through all of the data, in a table to find the rows that satisfy the query. Normally happen on table without any indices.

Clustered Index Scan:

Full scan in a clustered index table to find the rows that satisfy the query.

Non-clustered Index Scan:

Full scan in a non-clustered index table to find the rows that satisfy the query. Probably happens mostly in query that select all columns.

Clustered Index Seek:

Scan only on clustered index (as opposed to whole table).

Non-clustered Index Seek:

Scan only on non-clustered index (as opposed to whole table).

Key Lookup (Bookmark Lookup):

Happens when the query requests data from columns not present in the clustered index.

RID Lookup:

Happens when the query requests data from columns not present in the non-clustered index.

For understanding of some of these terms with example, this post by Greg Robidoux, “Understanding SQL Server Indexing” is very helpful.

 
Leave a comment

Posted by on August 6, 2011 in General

 

Tags: , , ,

 
%d bloggers like this: