Caveman's Blog

My commitment to learning.

SQL Server: Table Partitions as an archiving solution

leave a comment »


Problem Statement

How can you design an archiving solution on a large table without deleting any data and also achieve improved performance on CRUD operations (on the same table)? The assumption in this scenario is that the most recent data would be accessed more often that the older data.

Solution

Archiving solutions can be of two types; 1) passive and 2) active. A passive solution is one where the historic data is archived in another table on another database, making the data unavailable. An active solution is one where the historic data is archived and will still be available for access without much of an impact on the application performance. A large table typically contains millions of rows and probable has a size that runs into several gigabytes. Just the size of the table makes it very expensive to perform CRUD operations and difficult to maintain indexes.

Table Partitioning in SQL Server 2005 and up lets a table data and indexes to be stored in several smaller partitions. This feature is very useful in providing a way to easily maintain and perform database operations on that table. Each partition will be stored in a different file which can be part of a filegroup. Data is distributed evenly between the files in a filegroup. Allowed columns can be used as a partition key which is the criteria for partitioning. You will be able to define the boundaries of a partition by defining the limits of the partition key. This division is based on how you access the rows of the table. In addition to identifying the partition key, we also will want to include that key as part of the index and also partition the index. Partitioning the index is called as index alignment. This was when the index portion will be stored along with the data rows stored in the partition.

Dividing a table into several files gives us the flexibility of storing those files on separate drives. We can store the files that contain data from the recent years on faster drives as opposed to storing the older data on slower drives. Going with assumption in the problem statement that the most recent data is accessed more often that the older data, we will have improved the performance on this table because we will have faster response times thanks to the faster drives.

Note: All data types that are valid for use as index columns can be used as a partitioning column, except timestamp. The ntext, text, image, xml, varchar(max), nvarchar(max), or varbinary(max) data types cannot be specified. [1]

References:
1. Partitioned Tables and Indexes
2. Create Partitioned Tables and Indexes

Advertisements

Written by cavemansblog

July 2, 2013 at 9:32 pm

SQL Server: How to select data from executing dynamic SQL

leave a comment »


In his post I will show you how to select data from executing dynamic SQL. This method is handy in a scenario where you plan to create a Function Import (using EntityFramework [2]) for a stored procedure that returns a result set based on the execution of a dynamic SQL. Capturing the result in a temp table or a table variable will allow EF to define the return type of the Function Import. In my example I am using table variables as they are easier to maintain than temp tables which have a physical presence. The following code returns one row of employee details that was insert into the table variable using dynamic SQL. Dynamic SQL can be executed using exec [3] statement or the sp_exectuesql system stored procedure.

declare @employee table(id int, name nvarchar(50))
declare @str nvarchar(1000) = 'select 1, ''Joe'''

insert into @employee(id, name)
exec(@str)

select * from @employee

DNYSQL-1

Here is another way to capture the output where sp_executesql is used to execute a dynamic sql string. The following code snippet returns the total numbers View in the database from the sys.objects object catalog view [4].

Declare @strSQL as  nvarchar(100)
Declare @strParam as nvarchar(100)
Declare @XType as nvarchar(2) = 'V'
Declare @rtnCount as varchar(100)

Set @strSQL = 'Select @ObjCount=count(*) from Sysobjects Where xType=@xType'
Set @strParam = '@ObjCount int output, @xType nvarchar(2)'
execute sp_executesql @strSQL, @strParam, @rtnCount  output ,@xType

Select @rtnCount [View Count]

DNYSQL-2

References:
1. sp_executesql (Transact-SQL)
2. Entity Framework
3. EXECUTE (Transact-SQL)
4. Object Catalog Views (Transact-SQL)

Written by cavemansblog

May 22, 2013 at 12:13 pm

Paired Progamming

with one comment


Paired Programming is an Agile software development technique where two programmers work together on a workstation. One, the driver, writes code while the other, the observer, pointer or navigator,[1] reviews each line of code as it is typed in. The two programmers switch roles frequently. The goal of this technique is lower defects, improve quality and develop faster.

An analogy to this technique is the people who fly commercial aircraft; the Captain, also referred to as the pilot and the first officer, also referred to as the copilot. Both the pilot and first officer are fully qualified to fly the plane at all times, depending on the length of the flight both of the crew will alternate command. If a flight is going from place A to place B to place C, the captain will fly the first leg and the first officer will fly the second leg. The pilot who is not flying is still busy, working the communication radios and navigational computers, etc.

Similarly, when the driver is working on the code, the other programmer observes, guides, thinks about the how to test and identifies any potential defects, After a while the roles are reversed and the development continues. This collaboration brings in the best of the ideas from both the members and typically helps in writing better quality code. Although that is the expectation, the expected output of this technique depends on how well both the programmers are skilled and how efficiently they communicate with each other. No communication would result in less productivity and which defeats the purpose of this technique. Forming new pairs frequently through out the development cycle with help is knowledge sharing in the team. This technique is suggested to be employed by a pair of experienced programmers who are at a similar skill level.

References:
1. Paired Programming

Written by cavemansblog

April 14, 2013 at 9:54 am

Posted in Agile

Tagged with ,

File upload – attachment size validation

with one comment


Restricting the size of a file upload is an important validation that needs to be performed by an online application so avoid the risk of filling up the server disk space by a malicious intent. ASP.Net provides an upload control that only provides a server side validation of file size. By the time the control validates the size of the uploaded file, the physical file would have been already been copied to the server, which is tool late in avoiding the issue.

Client side technologies comes to the rescue in this scenario, where the validation of an attachment size can be implemented using a browser run-time like Flash, Silverlight, ActiveX, HTML5 etc. This way, if attempts were made to upload files with unsupported sizes, the run-time plug-in can thwart the attempt without any impact on your web server. Following are two free tools that can be employed for this purpose:

  • SWFUpload is a flash based tool.
  • PLUpload is a versatile plugin that can support multiple run-times. This plugin slices a large file in small chunks and will send them out one by one to the server. You can then safely collect them on the server and combine into the original file. The size of the chunks and the acceptable file formats can be defined in the plugin UI definition.

We have implemented PLUpload with good success. This plugin also support multiple file uploads. Visit the plugin homepage to see the other rich features that are supported. The online forum is a treasure trove, where you can find the various implementations, code snippets and will be able to participate in contributing to the community.

PLUpload

References:
1. SWFUpload
2. PLUpload
3. PLUpload Forums

Written by cavemansblog

February 20, 2013 at 10:02 am

SQL Server – Clean Buffers

with one comment


Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server. To drop clean buffers from the buffer pool, first use CHECKPOINT to produce a cold buffer cache. This forces all dirty pages for the current database to be written to disk and cleans the buffers. After you do this, you can issue DBCC DROPCLEANBUFFERS command to remove all buffers from the buffer pool. [1]

CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO

It is recommended these commands should not be executed in a production environment where the SQL Server caching helps in gaining performance. Running these commands could adversely impact the server performance.

Reference:
1. DBCC DROPCLEANBUFFERS (Transact-SQL)

Written by cavemansblog

December 10, 2012 at 11:34 pm

Posted in Uncategorized

Tagged with ,

Sql Server: Temp table vs table variable

leave a comment »


Temp table vs table variable. One question that lingers in my mind is; how to decide between using one over the other?  I will present some points that I have gathered that could aid in helping you make a decision in picking one over the other:

1. Reseed the index of a table variable is not allowed.

2. We will not be able to explicitly create Indexes on a table variable.

3. Altering the schema of a table variable is not possible

4. Table variables do not have to drop it after usage, they will be automatically cleaned up when they are out of scope.

5. Transactions involving table variables last only for the duration of an update on the table variable. Therefore, table variables require less locking and logging resources. They are not affected by transaction rollbacks.

6. Table variables contribute to more efficient Query Processing.

7. CHECK constraints, DEFAULT values and computed columns in the table type declaration cannot call user-defined functions.

8. The table declaration includes column definitions, names, data types, and constraints. The only constraint types allowed are PRIMARY KEY, UNIQUE, NULL, and CHECK.

Table variables will offer best performance when the data size is small and the . The next time you have to make a choice, choose wisely between the two and make a wise choice for the usage to derive optimal performance from your queries.

References:
1. Table (Transact SQL) – Microsoft SQL Server

Written by cavemansblog

October 24, 2012 at 12:03 am

SQL Server: Case sensitive string comparison

leave a comment »


Here is a useful tidbit. We can use one of the following two methods to perform a case sensitive string/field comparison when a SQL Server database does not support case sensitivity.

declare @a as nvarchar(20) = 'Test'
declare @b as nvarchar(20) = 'TEST'

--Method1:
if(convert(varbinary,@a) = convert(varbinary,@b))
   select 'identical'
else
   select 'non-identical'

--Method 2:
if(@a = @b COLLATE Latin1_General_CS_AS)
   select 'identical'
else
   select 'non-identical'

case_sensitive_comparison

Written by cavemansblog

August 15, 2012 at 10:26 pm