Bulk Loading data into COLUMNSTORE table

Bulk loading is the most performant way to move data into a columnstore index because it operates on batches of rows. Bulk loading fills rowgroups to maximum capacity and compresses them directly into the columnstore. Only rows at the end of a load that don’t meet the minimum of 102,400 rows per rowgroup go to the deltastore. For understanding bulk load and minimal logging see Fastest Data Loading using Bulk Load and Minimal Logging
Read more Bulk Loading data into COLUMNSTORE table

Bulk Loading data into HEAP versus CLUSTERED Table

Bulk Loading into HEAP

Generally, the fastest way to load data into SQL Server table is by Bulk Loading data into HEAP using  buy avodart canada TABLOCK hint on destination table which facilitates minimally logging. For more details see Fastest Data Loading using Bulk Load and Minimal Logging 
The few constraints for loading data minimally logged using TABLOCK hint:

  • The database must be in simple or bulk logged recovery model.
  • The destination table must be without Clustered Index.
  • There must be no NON-Clustered index on the destination table.

Read more Bulk Loading data into HEAP versus CLUSTERED Table

SQL Server Transactional Replication

Replication is one of the High Availability features available in SQL Server. Transactional Replication is used when DML or DDL schema changes performed on an object of a database on one server needs to be reflected on the database residing on another server. This change happens almost in real time (i.e. within seconds). In this article, I will demonstrate the step by step approach to configuring transactional replication in SQL Server 2012.
Read more SQL Server Transactional Replication

Memory Optimized Table – ACID Compliance

Memory Optimized Tables (MOT) are fully ACID (Atomicity, Consistency, Isolation and Durability) compliant data storage structures. They are stored as an organized sets of rows, and follow a special BW-Tree structure in order to support non-clustered hash indexes (At least one index is needed for MOTs to link rows with each other). Data is stored mainly within the program’s memory, where it is accessible to users. The data can also be copied onto the system’s hard drive by changing durability of MOT table. Each table has multiple versions of a single row, which allows for a row to be concurrently read and written. Memory optimized tables are durable, transactional and accessible through Transact-SQL. These features improves a MOT’s performance by 5 to 20 times. Lets see all the above features in more detail.

Read more Memory Optimized Table – ACID Compliance

Understanding Transacation Log and Recovery Model

Every SQL Server database has a transaction log that records all transactions and the database modifications that are made by each transaction. The transaction log is a critical component of the database and, if there is a system failure, the transaction log might be required to bring your database back to a consistent state. This guide provides information about the architecture of the transaction log and its various different uses in SQL Server.

Read more Understanding Transacation Log and Recovery Model

Fastest Data Loading using Bulk Load and Minimal Logging

Understanding Bulk Load Operations

There are two types of INSERTs in SQL server, first is singleton INSERT which inserts rows one by one and second type is BULK INSERT which feeds a continuous stream of data into a table, so this type of INSERT is much faster than singleton inserts. Read more Fastest Data Loading using Bulk Load and Minimal Logging

CS #2 Using COVERING INDEX for improving performance

erythromycin zinc acetate lotion buy Problem Statement

Stored procedure http://readysetincorporate.com/faq-items/is-my-business-big-enough-to-justify-the-additional-overhead-of-a-corporation-or-llc/ CS2_COVERING_INDEX is running slow for some input parameters combinations and running fine for other input parameters.

CREATE PROC CS2_COVERING_INDEX 
     @start_prod_id INT,
        @end_prod_id   INT
AS
  BEGIN
    SELECT TOP 10 TransactionID,ProductID,TransactionDate
    FROM bigTransactionHistory
    WHERE ProductID BETWEEN @start_prod_id AND @end_prod_id
    ORDER BY ActualCost DESC;
 END;
GO

Read more CS #2 Using COVERING INDEX for improving performance

Table Partitioning: Adding\Removing partitions using SPLIT, MERGE

In this tip we’ll see one of the advantage of partitioning a very large table, which is the ability to add or remove partitions instantaneously to help you with sliding window scenarios.

Lets first create partition function and partition scheme. Here i m using monthly partition scheme.

CREATE PARTITION FUNCTION [PF_MONTHLY](date) AS RANGE LEFT FOR VALUES 
   ('2016-01-31', '2016-02-29', N'2016-03-31', N'2016-04-30', N'2016-05-31')

CREATE PARTITION SCHEME [PS_MONTHLY] AS PARTITION [PF_MONTHLY] 
   ALL TO ([XYZ_FILE_GROUP])

Now lets add new partition for next month using partition split.

ALTER PARTITION SCHEME ps_monthly NEXT used XYZ_FILE_GROUP

ALTER PARTITION FUNCTION pf_function() SPLIT RANGE ('2016-06-30')