Hash functions to improve string comparison and DB desgin

Scenario

I have gone through some difficult experience to compare strings much time during database design and Querying and think about we need to get some different solution to perform same operation. Many times there are requirements to create Varchar field as Primary column like Account Number in Chart of Account table in Accounts software or Comments column in any table with varchar(max) or (100) etc.

Problem

When we try to join string based column or use in our where clause even to find a single record with like or equals clause it increase our query cost. I came across same scenario in recent time to compare a string based column to just check whether it equals to parameter string value or not and it took 76% of total query cost. SQL SERVER Optimizer hints to create a cover index using Primary Key plus this column which improve query performance by at least 50% but at the same time I came across Hash Key function that satisfied my needs, storage and optimization. So I am going to discuss what is Hash key and how can we implement it using SQL SERVER 2008 and above.I have gathered information from many sources and msdn is one of them.

What is Hash Function

Hash is the value (int or varbinary) result of an algorithm (known as a hash function) applied to a given string. You just need to provide your string as input and you will get a unique hash value as an output. If we provide a complete page string to this function and then change just a character to pass value to it. It will return different values. There is small possibility to repeat same value and this will known as hash collision.

Where can I use Hash Function?

·         Security implementation as encryption of string data.

·         Reduce network traffic for cross DB queries because of small size required to compare instead of string values.

·         Less space requires to store and campare values because it returns int and binary values instead of string characters.

·         Performance increase due to small db types and indexes uses if we implement index on it.

·         Avoid whole string comparisons and requires comparing just int values as checksum.

·         If we create hash column in our DB design along with string columns then it is easy for us to implement joins on these column that will act like string joins as these are hash values of string values.

Careful using Hash Function

·         Hash collision may return more than your expected result set by returning same hash value. For this reason, CheckSum might return more values then you expected but if you want to use this column to identify your column changes then consider HashBytes. When an MD5 hash algorithm is specified, the probability of HashBytes returning the same result for two different inputs is much lower than that of CHECKSUM.

·          

Types of Hash function

• Hash Keys in Database Design
• CheckSum()
• HashBytes

For detail about these functions please go to 

(Hash function) Shamas Saeed Professional SQL SERVER Blog 

SQL SERVER Express advance features (supported and Unsupported Features)

We don't need to install complete SQL SERVER Engine to build reports SSRS tool, Management tools and other advance features as we do in previous versions.

More detail can be viewed from this link
http://shamas-saeed.blogspot.com/2012/05/sql-server-2008-express-with-advance.html

I got this detail from different sources of msdn linked below.

You can download it from this link
http://www.microsoft.com/en-us/download/details.aspx?id=1842

features supported and unsupported by SSRS 2008 Express with Advance Edition is

http://msdn.microsoft.com/en-us/library/cc281020(v=sql.100).aspx

Feature Summary

SQL Server Express provides the following Reporting Services functionality:

·         On-demand report processing for each user who views a report. When a user opens a report, the report is initialized, the query is processed, data is merged into the report layout, and the report is rendered into a presentation format.

·         Rendering formats are available for HTML, Acrobat, and Excel.

·         Report data sources must be SQL Server relational databases that run locally in SQL Server Express.

·         Report server management and report viewing are supported through Report Manager.

·         Configuration is supported through the Reporting Services Configuration tool.

·         Rs.exe, rsconfig.exe, and rskeymgmt.exe command line utilities are available in SQL Server Express.

·         Windows Authentication and predefined roles are used to map existing group and user accounts to a named collection of operations.

Unsupported Features

Other editions of SQL Server include a larger set of Reporting Services features. The following list describes the features that are documented in SQL Server Books Online, but cannot be used in this edition:

·         Scheduled report processing, caching, snapshots, subscriptions, and delivery are not supported.

·         Analysis Services, Oracle, XML, SAP, SQL Server Integration Services (SSIS), OLE DB, and ODBC data sources are not supported.

·         Remote data sources are not supported. Reports that are hosted in a SQL Server Express report server must retrieve SQL Server relational data from a local SQL Server Express Database Engine instance.

·         Ad hoc reporting through semantic models and Report Builder is not supported.

·         TIFF (Image), XML, and CSV rendering extensions are not supported.

·         The Reporting Services API extensible platform for delivery, data processing, rendering, and security is not supported.

·         Custom authentication extensions and custom role assignments are not supported. You must map existing Windows domain user and group accounts to predefined role definitions.

·         Custom report items are not supported.

·         Managing a long-running report process is not supported. Specifically, you cannot use the Manage Jobs feature in Report Manager to stop report processing.

·         Scale-out deployment is not supported.

·         SharePoint integrated mode is not supported.

·         Report Builder 2.0 is not supported. Report Builder 2.0 can connect to a SQL Server Express database on the local computer or on a remote report server.

list of Supported features and not Supported features in SQL SERVER Express 2008
http://msdn.microsoft.com/en-us/library/ms365248(v=sql.100).aspx

SQL Server Features that Are Supported in SQL Server Express

SQL Server Express supports most of the features and functionality of SQL Server 2008. This includes the following:

Stored procedures	SQL Server Configuration Manager
Views	Replication (as a subscriber only)
Triggers	Advanced Query Optimizer
Cursors	SMO/RMO
sqlcmd and osql utilities	Integration with Visual Studio
Snapshot isolation Levels	Service Broker (as a client only)¹
Native XML support. This includes XQuery and XML schemas	SQL CLR
Transact-SQL language support	Multiple Active Result Sets (MARS)
Dedicated Administrator Connection²	Full text search
Subset of Reporting Services features³	Import/Export Wizard

SQL Server Features that Are Not Supported in SQL Server Express

The following table lists the additional SQL Server 2008 database features that are not supported in this version of SQL Server Express. It also lists database features from earlier versions of SQL Server that are not supported.

SQL Server features not supported in SQL Server Express	SQL Server features from earlier versions not supported in SQL Server Express
Database mirroring	SQL Mail
Online restore	Failover clustering
Database snapshot	Distributed partitioned views
Parallel index operations	VIA protocol support
Mirrored media sets	Log shipping
Partitioning	Parallel DBCC
Address Windowing Extensions (AWE)	Parallel Create Index
Hot-add memory	Enhanced Read Ahead and Scan
Native HTTP SOAP access	Indexed views (materialized views)
SQL Mail and Database Mail	Partitioned views
Online Index Operations	-
SQL Server Agent and SQL Server Agent Service	-

Except and Intersect in SQL SERVER

Except and Intersect can be used to returns distinct values by comparing the results of two queries.

The basic rules for combining the result sets of two queries that use EXCEPT or INTERSECT are the following:

• The number and the order of the columns must be the same in all queries.
• The data types must be compatible.

If EXCEPT or INTERSECT is used together with other operators in an expression, it is evaluated in the context of the following precedence:

1. Expressions in parentheses
2. The INTERSECT operand
3. EXCEPT and UNION evaluated from left to right based on their position in the expression

------------------------------ Code Sample -----------------------------------------

Declare @tbl Table(InstructorID int,CompanyID int,btactive bit,bitprimary bit)

Insert into @tbl values (1,1,1,0),(2,1,1,1),(3,1,1,0),(4,2,1,0),(2,2,1,1),(1,3,1,0)

select * from @tbl

------------------------------ Code Sample -----------------------------------------

I have created a declare table @tbl with four columns and insert some values in it. Now i want to return all those compayIds that are active with bitPrimary = 0 but the result set must not include those companyIds which have bitPrimay= 1 value as well.

If i use simple query to return bitprimary = 0 I got following result

If we look at the results CompanyID 1 and 2 have data against bitPrimary = 0. So according to my requirement i need only to get 3 companyID in my result set. Now look at the use of EXCEPT operator. 

EXCEPT:

EXCEPT returns any distinct values from the left query that are not also found on the right query. When an EXCEPT operation is displayed by using the Graphical Showplan feature in SQL Server Management Studio, the operation appears as a left anti semi join.

------------------------------ Code Sample -----------------------------------------

select CompanyID

from @tbl

where btactive = 1 and bitprimary = 0

EXCEPT

select CompanyID

from @tbl

where btactive = 1 and bitprimary = 1

------------------------------ Code Sample -----------------------------------------

look at the pic above. I have attached result set on top right corner which shows CompanyID 3 and this is what exactly I need to get.

We can also get similar results by using EXCEPT -- NOT EXISTS -- NOT IN. But as per many MVPs EXCEPT is best as performance then others.

Now what if i need to get all those compayIds that are active with bitPrimary = 0 but the result set must include those companyIds which have bitPrimay= 1 as well. Now i use INTERSECT

INTERSECT:

INTERSECT returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand. When an  INTERSECT operation is displayed by using the Graphical Showplan feature in SQL Server Management Studio, the operation appears as a left semi join.

------------------------------ Code Sample -----------------------------------------

select CompanyID

from @tbl

where btactive = 1 and bitprimary = 0

INTERSECT

select CompanyID

from @tbl

where btactive = 1 and bitprimary = 1

------------------------------ Code Sample -----------------------------------------

look at the pic above. Result set shows CompanyID 1 and 2 because both have btPrimary = 0 and 1 but CompanyID 3 does not have btPrimary = 1 so that ommits.

We can also get similar results by using INTERSECT -- LEFT OUTER JOIN -- FULL OUTER JOIN.

BitWise Operators in SQL SERVER

Bitwise operators perform bit manipulations between two expressions of any of the data types of the integer data type category.The operands for bitwise operators can be any one of the data types of the integer or binary string data type categories (except for the image data type)

There are three types of BitWise operators

• & (Bitwise AND)  
• ~ (Bitwise NOT) 
• | (Bitwise OR) 
• ^ (Bitwise Exclusive OR)

We will use following table and data to execute our examples

For code and further detail about Bitwise operator. Please visit this blog link

http://shamas-saeed.blogspot.com/2012/05/bitwise-operators-in-sql-server.html

& (Bitwise AND)

~ (Bitwise NOT)

| (Bitwise OR) 

^ (Bitwise Exclusive OR)

..............................................................................

................................................................................\

Timestamp and RowVersion Usage and Behaviour in SQL SERVER

I have gone through a different experience today. On of my coleague ask me to get latest users data from a table. when i looked into table structure it does not have any date or time datatype. When i raise the question that if he wants me to return data on the basis of Id he replies no i have defined a timestamp column use that by using date time conversion. When i tried there is an error message that database engine is unable to convert timestamp into date time. On most of the times people think that timestamp is some type of date and time data type and can return date. 

In this blog we will look into timestamp datatype, its usage and what is the difference between timestamp and rowversion. From microsoft fourms i have got following details.

Timestamp

Defination

Timestamp is a data type that exposes automatically generated, unique binary numbers within a database. Timestamp is generally used as a mechanism for version-stamping table rows like replication. 

Timestamp as DateTime;

The timestamp data type is just an incrementing number and does not preserve a date or a time. To record a date or time, use a datetime data type.

Look at the following timestamp behavior.

• Timestamp as Database timestamp:
• Table can have one timestamp column;
• Timestamp as Primary key;
• Timestamp as Dynamic Cursor;
• Timestamp as Index key;

Timestamp can be good in following scnarios

• Timpstamp as value changes identification;
• Timestamp as rowversion;
• Timestamp can be duplicate;
• Timestamp as Null and Not Null;
• Timpstamp with default column name;

------------------------ Sample Code ---------------------------------------------

Declare @tbl table (id int identity(1,1),date datetime,stamp timestamp)
Insert into @tbl values (getdate(),default)
select * from @tbl


Update @tbl set date = getdate() + 1
select * from @tbl
----------------------------------------------------------------------------------------

For detail about each topic in this post go to following link

timestamp, rowversion and datetime

Failover techniques for Database Mirroring using SQL SERVER 2008

Fail-over techniques in database mirroring.There are different techniques for database mirroring.

1. Automatic fail over
2. Manual fail over
3. Forced fail over

The following table summarizes which forms of failover are supported under each of the operating modes. 

	High performance	High-safety mode without a witness	High-safety mode with a witness
Automatic failover	No	No	Yes
Manual failover	No	Yes	Yes
Forced service	Yes	Yes	No

Automatic Fail-Over:

To test database mirroring auto fail over solution you need to restart database engine and your Principal and Mirror servers will be reversed but you need to set your mirroring solution as (Principal – Witness - Mirror). You can view how we can test auto fail over solution look at this link.

http://shamas-saeed.blogspot.com/2012/04/failover-techniques-using-database.html

Manual Fail-Over:

During a manual failover, the principal and mirror server roles are swapped for the database on which the failover occurs. The mirror database becomes the principal database and the principal database becomes the mirror. For example, the following table shows the how a manual failover swaps the roles of two mirroring partners: System

http://shamas-saeed.blogspot.com/2012/04/fail-over-techniques-using-sql-server.html

Forced Fail-Over:

We are now looking into Forced Service for database mirroring. This option is available against high safety but automatic failover is not available.  In the event of failure of the principal, the database service becomes unavailable. You need manual intervention to make the database service available.

http://shamas-saeed.blogspot.com/2012/05/fail-over-techniques-for-database.html