SQL Server - OLE DB Deprecated :(

As you may already know, the SQL Native Client OLE DB provider is being deprecated. This doesn't affect other OLE DB providers or the OLE DB API. Also, the SSIS OLE DB components are not being deprecated.

In SQL Server 2012 SSIS, you can continue to use OLE DB connections and will need to for components such as the Lookup transformation and the Slowly Changing Dimension transformation that require OLE DB. In the post-Denali release, you’ll be able to upgrade and continue to operate these packages without needing to do additional work to explicitly remove OLE DB from the packages.

Read More

SQL Server - Result paganation in sql server

A lot of SQL Server database users at some time faced a big obstacle, especially web sites programmers when need to read data from the database in case of a partial data was a fairly large size. After a long and arduous journey made in the search for the best method that can solve the problem very haunting, I've experimenting with dozens of ways that were not satisfactory at first, especially if the data is very large size.

In fact,i did't invent magic solution, but I found the best located after many modifications and tests, all web solutions was talked about using  these methods (ROW_NUMBER() , TOP Rows ,CTE and Temp tables) in sql server 2005 and older versions wich i prefer using TOP method to done this job , because it's flexible and no limitation or conflicts for using TOP with your query criteria's and orders data results .

Using TOP IN SQL Server 2005

i prefer using sql stored procedure with parameters like @page_num , @page_size and @query_type that i use @query type to switch between counting results and query reaults it self .

Create PROCEDURE [dbo].[GetArchive]
(@username NVARCHAR(100),@page_num int,@page_size int,@proc_type nvarchar(20))AS
BEGIN


declare @page_count int ,@StartRow int , @EndRow int ,@sql nvarchar(max)
 

if (@proc_type='get_count')
begin
 set @sql = 'SELECT Count(*) as AllCount FROM Archive where id > 0'


end
else
begin
 set @sql = 'SELECT TOP ' + cast(@page_size as nvarchar(100)) 
 set @sql = @sql + ' Name,Mobile,Email,[Datetime] '
 set @sql = @sql +' FROM Archive '
 set @sql = @sql + 'WHERE ID NOT IN(SELECT TOP ('+cast(@page_size as nvarchar(100))+' * ('+cast(@page_num as nvarchar(100))+' - 1)) ID FROM Archive where id > 0 AND Username ='''+@username+''' order by id desc) '
end
set @sql = @sql + ' and username = '''+@username +'''';


if (@proc_type<>'get_count')
   set @sql = @sql + ' order by id desc '




exec (@sql)

 

END

Executing procedure will generate sql query like this

SELECT TOP 10 Name,Mobile,Email,[Datetime] FROM Archive WHERE ID NOT IN(SELECT TOP (10 * (2 - 1)) ID FROM Archive where id > 0 AND Username ='User' order by id desc)  and username = 'User' order by id desc 

Paganation IN SQL Server 2012

Sql server 2012 intreduce new keyword to do this job in easy and fast way like any other databsaes especially LIMIT in Mysql, using OFFSET ended this long conflict by add this keyword to your query

SELECT
     *
FROM  [dbo].[Archive]
ORDER BY id
OFFSET 10 ROWS
FETCH NEXT 20 ROWS ONLY

Read More

SQL Server - Table Partitioning technique

Technical concept :

partitioning in simple word is spreads your data in multi file groups , file groups it's physical files on your Hard drive .

Usage:
This technique is used heavily in case of very large tables which are hard to get rid of this data on a regular basis, so it would be difficult to work out on the tables of millions of data that are used only when needed.

Therefore stems need to provide a mechanism to keep all the data available, while maintaining the good
performance of the database.

Partitioning a SQL Server database table is four-steps process:

1-Create the partition function
2-Create File Groups
3-Create the partition scheme
4-Partition the table


The remainder of this article explores each of those steps in further detail.

Step 1: Creating a Partition Function

partition defines the function how you want SQL Server to split the data. At this point, we are not concerned with any particular table, we select only the general technique to split the data.

Define partitions to determine the limits of each partitions .For example, suppose we have a customer table contains information on all of our customers, identified by a unique customer number, ranging from 1 to 1,000,000. We may decide to split the table into four partitions equally spaced, using the following partition function (I call it customer_func):

CREATE PARTITION FUNCTION customer_func (int 
AS RANGE RIGHT 
FOR VALUES (250000, 500000, 750000)

These limits determine four partitions. The first contains all values ​​less than 250,000. The second contains values ​​between 250,000 and 499,999. The third contains values ​​between 500 000 and 749 999. All values ​​greater than or equal to 750,000 to go in the fourth section.

Note that I used the phrase "RANGE RIGHT" in this example. This indicates that the threshold value itself should go in the partition on the right side. Alternatively, if you have used the "LEFT RANGE", the first partition has included all values ​​less than [U] or equal to [/ U] 250,000; second section will be included values ​​between 250,001 and 500,000, and so on .

Step 2:Create File Groups

Create and defines 4 file groups .

ALTER DATABASE Customers
ADD FILEGROUP fg1;
GO

ALTER DATABASE Customers
ADD FILEGROUP fg2;
GO

ALTER DATABASE Customers
ADD FILEGROUP fg3;
GO

ALTER DATABASE Customers
ADD FILEGROUP fg4;
GO

ALTER DATABASE Customers 
ADD FILE 
(
    NAME = fg1_dat,
    FILENAME = 'C:\Databases\Customersdb\fg1_dat.ndf',
    SIZE = 5MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP fg1;

ALTER DATABASE Customers 
ADD FILE 
(
    NAME = fg2_dat,
    FILENAME = 'C:\Databases\Customersdb\fg2_dat.ndf',
    SIZE = 5MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP fg2;


ALTER DATABASE Customers 
ADD FILE 
(
    NAME = fg3_dat,
    FILENAME = 'C:\Databases\Customersdb\fg3_dat.ndf',
    SIZE = 5MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP fg3;


ALTER DATABASE Customers 
ADD FILE 
(
    NAME = fg4_dat,
    FILENAME = 'C:\Databases\Customersdb\fg4_dat.ndf',
    SIZE = 5MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP fg4;

Step 3: Creating a Partition Scheme

Once you have a partition function describing [u]how[/u] you want to split your data, you need to create a partition scheme defining [u]where[/u] you want to partition it. This is a straightforward process that links partitions to filegroups. For example, if I had four filegroups named "fg1" through "fg4", the following partition scheme would do the trick:

 CREATE PARTITION SCHEME customer_partscheme

 AS PARTITION customer_func

 TO (fg1, fg2, fg3, fg4)

Notes that now link the partition function of the partition scheme, but we are still not linked to the partition scheme to any specific database table. Since the re-use of force comes into play. We can use this partition scheme (or simply a function of the division, and if we wish) on any number of database tables.

Step 4: Partitioning a Table

After defining the partition scheme, and you are now ready to create a partitioned table. This is the simplest step of the process. You can simply add the condition "ON" for the statement to create a table to determine the partition scheme and table column that applies to it. You do not need to determine the function of the partition because the partition scheme sets already.

For example, if you want to create the customer table using the partition scheme we have, and you use Transact-SQL statement following:

 CREATE TABLE customers (FirstName nvarchar(40), LastName nvarchar(40), CustomerNumber int)

 ON customer_partscheme (CustomerNumber)

That’s everything you need to know about partitioning tables in Microsoft SQL Server! Remember to leverage the power of reusability by writing generic partition functions and partition schemes that might apply to multiple tables!

Read More