BIT Column Indexes

There are two myths around BIT column indexes.

Myth #1. Cannot create indexes on BIT columns.

Not sure how this was evolved. But this not absolutely false. Let us try it.

USE tempdb
GO

CREATE TABLE Employee
(ID INT IDENTITY PRIMARY KEY CLUSTERED,
Name Varchar(50),
Status BIT
)

CREATE INDEX idx_employee_status ON Employee(Status)

Above statement is successful and you can create the index is created.

I verified this in SQL Server 2000 SP4 and it was successful.

Myth #2. BIT Column indexes are not useful.

Above argument has a base. BIT column can have possible three values, 0, 1 and NULL. Therefore index selectivity is low.  For example, consider the above table which has 10 million rows and status will say whether these are active or not.  In large table, active records will be very less so active = 0X1 will be highly selective.

SQL Server 2000 Losing Security Support on April 9

SQL Server 2000 is at the end of its product lifecycle and will lose its "extended support" from Microsoft on Tuesday, April 9.

The loss of extended support means no more security updates will be delivered to customers via Microsoft's update service. Remedies for organizations still using SQL Server 2000 are to upgrade the product (Microsoft is recommending moving to its current SQL Server 2012 product) or pay for "custom support" through Microsoft Premier Support services, according to a Microsoft announcement.

It's possible to continue to run SQL Server 2000 after April 9, but that approach isn't recommended by Microsoft for security reasons. Self-help resources, such as Knowledge Base articles and troubleshooting tools, will continue to be available online for a minimum of 12 months.

http://redmondmag.com/articles/2013/04/05/sql-server-2000-support.aspx

SET ANSI_NULL_DFLT_ON & SET ANSI_NULL_DFLT_OFF

This is the ninth post of SET Statement series.

By default when you add a column to the table without specifying the nullability , column will be nullable.

If you run this script,

CREATE TABLE Test
(ID INT)

table will be as follows.

You can change the behavior of this by SET ANSI_NULL_DEFT_OFF to ON as shown below.

SET ANSI_NULL_DFLT_OFF ON

CREATE TABLE Test
(ID INT)

You can see that nullability of the column is off.

When SET ANSI_NULL_DFLT_ON is ON, new columns created allow null values if the nullability status of the column is not explicitly specified. When SET ANSI_NULL_DFLT_OFF is ON, new columns created does not allow null values if the nullability status of the column is not explicitly specified.

SET ANSI_NULL_DFLT_ON does not affect columns created with an explicit NULL or NOT NULL.

Both SET ANSI_NULL_DFLT_OFF and SET ANSI_NULL_DFLT_ON cannot be set ON at the same time. If one option is set ON, the other option is set OFF.

If the both options are off , is_ansi_null_default_on value of the database will be taken when tables are created.

SELECT name,is_ansi_null_default_on FROM sys.databases

You can change this option from the database options page as shown below.

The setting of SET ANSI_NULL_DFLT_ON does not apply when tables are created using the SELECT INTO statement. It applies only for CREATE TABLE and ALTER TABLE statements.

SET IMPLICIT_TRANSACTIONS ON

SET NOCOUNT

SET DEADLOCK_PRIORITY

SET CONCAT_NULL_YIELDS_NULL

SET FORCEPLAN

SET PARSEONLY

SET IDENTITY_INSERT

SET FMTONLY

MySQL's creator on why the future belongs to MariaDB

It's fair to say that MySQL creator Michael "Monty" Widenius is not a fan of Oracle. When the company announced in April 2009 that it was purchasing Sun, Widenius saw a bleak future ahead for the (still) wildly popular open source database, which Sun had snapped up in 2008.

The day the Sun purchase was announced, Widenius responded in the tried and true open source fashion — he forked MySQL, launching MariaDB, and took a swathe of MySQL developers with him.

"Many of the original MySQL core developers, including me, didn't believe that Oracle would be a good owner of MySQL and we wanted to ensure that the MySQL code base would be free forever," Widenius explains.

Read more http://www.computerworld.com.au/article/457551/dead_database_walking_mysql_creator_why_future_belongs_mariadb/

Robomongo 0.6.8

Are you irritated by using command line to manage MongoDB, there is FREE tool named Robomongo.

You can download executable for windows and Mac from http://www.robomongo.org/ for free. According to the site, Linux version is coming.

From the left hand , you have the option of navigating to databases and collection.

Also, you can run the mongodb queries and retrieve the relevant data.

Apart from these option followings are the options you have. So , you can duplicate collection and manage Indexes and manage shards.

SET FMTONLY

This is the eight post of SET Statement series

when SET FMTONLY set to ON, query will return only metadata to the client. This setting can be used to test the format of the response without actually running the query.

USE AdventureWorks2012;
GO
SET FMTONLY ON;
GO
SELECT * 
FROM Production.Product;
GO
SET FMTONLY OFF;
GO

Output of this is,

So there will be no rows returned.

However, this SET option is set to depreciate with SQL Server 2012.

Previous posts of this series,

SET IMPLICIT_TRANSACTIONS ON

SET NOCOUNT

SET DEADLOCK_PRIORITY

SET CONCAT_NULL_YIELDS_NULL

SET FORCEPLAN

SET PARSEONLY

SET IDENTITY_INSERT

Microsoft Announces SQL Server 2012 R2 and SQL Server 2012 R3!

Today Microsoft has announced that the next two versions of SQL Server, which will include new features such as Hekaton and clustered, writable columnstore indexes, will adopt the R2 and R3 monikers respectively. Here is the tentative schedule:

• SQL Server 2012 R2
  • Q3 2013
  • Hekaton in-memory database
• SQL Server 2012 R3
  • Q1 2014
  • Clustered, writable columnstore indexes

Singapore NUS buys into Microsoft's big data vision

The National University of Singapore (NUS) today announced the successful implementation of SQL Server 2012 to give employees in its Centre for Instructional Technology (CIT) the ability to run hypotheses and validate IT proposals on a self-service basis.

The implementation of Microsoft's SQL Server 2012 took between three to six months, he revealed. Besides cutting down on time, the Power View dashboard for IVLE usage was significantly improved in that it was easily understood, configurable and managed almost entirely by end-users.

The Singapore university is one of the latest in the region to sign up for Microsoft's big data offerings. Arun Ulag, Microsoft's general manager for server & tools division in Asia-Pacific, said in the same briefing session Tuesday that Thailand's Department of Special Investigation (DSI) had implemented a big data system based on SQL Server 2012 and Apache Hadoop software to more effectively retrieve and correlate different sets of information stored up in its siloed databases.

Read more here

SET IDENTITY_INSERT

This is the seventh post of SET Statement series.

SET IDENTITY_INSERT is one of the most commonly used SET statement. SET IDENTITY_INSERT will be set to ON when you need to be inserted into the identity column of a table explicitly.

CREATE TABLE Department
(DeptID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
DepartmentName Varchar(50)
)


INSERT INTO Department
(DepartmentName)
VALUES
('Account'),
('Human Resources')

If you select from the table you will see DeptID column is populated with requital value.

Now what if we have a requirement of inserting deptid explicitly.

INSERT INTO Department
(DeptID,DepartmentName)
VALUES
(5,'Transport')

this will return an error as shown below.

Msg 544, Level 16, State 1, Line 2
Cannot insert explicit value for identity column in table 'Department' when IDENTITY_INSERT is set to OFF.

So to avoid this issue, you need to SET the IDENTITY_INSERT property on. This property is valid on for the session which enabled the IDENTITY_INSERT ON.

SET IDENTITY_INSERT  Department ON

INSERT INTO Department
(DeptID,DepartmentName)
VALUES
(5,'Transport')

SET IDENTITY_INSERT  Department OFF

If the value entered to the identity column is greater than the current identity value, after inserting new value value identity will be set to the new value. For example, if the current identity of the table is 5 and if you insert 100 to the identity column, next time you insert data without IDENTITY_INSERT option on, next value for the identity will be 101. However, if you are inserting a value less than the current identity value, current identity value remains.

To perform a IDENTITY INSERT you nee alter permission to the table not the write permission.

Previous posts of this series,

SET IMPLICIT_TRANSACTIONS ON

SET NOCOUNT

SET DEADLOCK_PRIORITY

SET CONCAT_NULL_YIELDS_NULL

SET FORCEPLAN

SET PARSEONLY

NoSQL Benchmarking

In this article it will reveal the characteristics of Cassandra, HBase and MongoDB identified through multiple workloads.