Are you going to save space using Bit Data Type? – Part 2

Yesterday I wrote a post with same title. In that post I mentioned using char(1) is also same like using one bit column in table. But it is from the perspective of storage space. But using numbers is less burden to Database engine. It means using 1 and 0 is better than using ‘Y’ and ‘N’. Also in comparing data or in sorting data it is easier.

If you are storing numbers as a text its not a good thing although you don’t do any mathematical operation with that text. First thing it takes more space. It takes 1 byte for every character, while integers take very less. And second thing if you store them as string it will have extra cost to manipulate, compare, sort, whatever you are going to do with them.

Advertisements

SA Account

As I read after installing SQL Server on a System it is a best practice to disable SA account. Then I asked from our architect, and one of prod DBAs. Then I got to know Yes it is a good practice if you disable SA Account, because it is a well known account. And also we can rename that account then there is no point to disable it.

Somehow finally I got to know that nobody should you SA account.

Are you going to save space using Bit Data Type?

If you have Yes or No data to store in DB, what will you select as the data type? Most of the times you will select “Bit”. Because it only takes 1 bit to store data. And it is the best data type for this situation. But it takes more than bit in SQL Server. If you have only one bit column in your table it will allocate 7 more extra bits to store your bit data.

And if you have two bit typed fields in your table it will waste 6 more bytes. But if you are using 8 bit typed fields in your table, it will not waste any space. And if you have only one such a binary values column in your table. Otherwise it will take space same as char(1) or tinyint. Although its name is bit it is not small like bit always.

Data Type details from sphelp

I wanted to learn about data types. Then I went to technet some how most of the important things were there in technet documentations. But I found another way to get figures from SQL Server. We can have details of data types from “sphelp”. You just have to give your data type as a parameter like below.

EXEC sp_help int

Then I executed this to every data type except few system data types. And I listed figures on categories which was in technet.

Exact Numerics

Type Name Length Precision Scale Nullable
bigint 8 19 0 Y
bit 1 1 Null Y
decimal 17 38 38 Y
int 4 10 0 Y
money 8 19 4 Y
numeric 17 38 38 Y
smallint 2 5 0 Y
smallmoney 4 10 4 Y
tinyint 1 3 0 Y

Approximate Numerics

Type Name Length Precision Scale Nullable
float 8 53 Null Y
real 4 24 Null Y

Date and Time

Type Name Length Precision Scale Nullable
date 3 10 0 Y
datetime2 8 27 7 Y
datetime 8 23 3 Y
datetimeoffset 10 34 7 Y
smalldatetime 4 16 0 Y
time 5 16 7 Y
Binary Strings
 
Type Name Length Precision Scale Nullable
binary 8000 8000 Null Y
image 16 2147483647 Null Y
varbinary 8000 8000 Null Y

Character Strings

Type Name Length Precision
char 8000 8000
varchar 8000 8000
text 16 2147483647

Unicode Character Strings

Type Name Length Precision
nchar 8000 4000
nvarchar 8000 4000
ntext 16 1073741823

Other Data Types

Type Name Length Precision Nullable
hierarchyid 892 892 Y
sql_variant 8016 0 Y
timestamp 8 8 N
uniqueidentifier 16 16 Y
xml -1 -1 Y

If I haven’t specified any attribute of those data types usually they have their default values. One special thing was there were only one data type which was not nullable. It is “timestamp”. And I was unable to take data of cursor and table from this method.

Did you tried to install SQL Server 2012 on Windows Server 2012?

Recently I tried to install SQL Server 2012 in Windows Server 2012 box. Unfortunately it was not easy to do that for me because setup generated few errors. And then I it displayed that .NET Framework 3.5 SP1 Feature was not able to activate. There were few reasons to occur that problem.

  • Windows Server 2012 was not activated.
  • Windows Server 2012 was unable to access internet (When it is activated).

So first I realized when I tried to manually activate this feature, that I have to activate my Windows Server 2012. Then I activated my Windows Server 2012. Then I again tried to activate .Net Framework 3.5. But unexpectedly again I got failed. Then I checked what was wrong with my computer and why this installation got failed although I had inserted the installation media.

Error was like this.

Troubleshooting101-01

Then I found in the internet that we have to show the path of the binaries to activate this feature.

1. Then I opened Server Manager and then Manage –> Add Roles and Features.

Troubleshooting101-02

2. I followed the wizard normally.

Troubleshooting101-03

Troubleshooting101-04

Troubleshooting101-05

Troubleshooting101-06

3. Then I selected .NET Framework 3.5 from features window.

Troubleshooting101-07

4. Then I got this dialog box.

Troubleshooting101-08

5. But be careful and don’t click “Install”. If you click Install it will look up for internet connection. If there is not internet connection it will fail your setup.  Click on “Specify an alternate source path”.

net3installsourcesrvmanager

6. And then give your installation media folder plus “\sources\sxs”. It may be a share or DVD no matter whatever it is. Then Click OK and Install. It will activate .NET Framework 3.5 on your Server 2012 installation.

I found those pictures from sqlcoffe.com. And I saw that he has also tried in this way and get failed because he hasn’t specify the path of installation media. But he has an alternate method. I’’ll share it here too.

He had opened a cmd window. and had typed this command.

dism /online /enable-feature /featurename:NetFx3 /source:d:\sources\sxs

Troubleshooting101-09

You can replace “d:\sourced\sxs\” with your path of installation media. Then I tried to install SQL Server 2012. Installation was successfully finished.

Pictures: http://www.sqlcoffee.com/Troubleshooting101.htm

Ranking in T-SQL

In Queries we need to rank and number records. SQL Server gives you few functions to rank your records.

RANK

Rank we can use  to rank data in normal way. In this ranking if we have two 1 next one will have rank 3. 

Name Marks Rank
John 75 1
Mark 75 1
Steve 64 3
Gates 54 4

 

USE [tempdb]
GO

--Creating table Marks
CREATE TABLE [MARKS]
(
    [Name] varchar(25),
    [Score] int
)
GO

--Inserting values into Mark table
INSERT INTO [MARKS]([Name], [Score]) 
    VALUES ('John',75),('Mark',75),('Steve',64),('Gates',54)
GO

--Ranking data over Score
SELECT *, RANK() OVER (ORDER BY [Score] DESC) AS [RANK] FROM MARKS

--Dropping table
DROP TABLE [MARKS]
GO

This ranks based on the column that you specify by OVER.

DENSE_RANK

Dense Rank is not missing ranks like in normal rank function. It has continuity over ranking.

Name Marks Dense Rank
John 75 1
Mark 75 1
Steve 64 2
Gates 54 3

 

USE [tempdb]
GO

--Creating table Marks
CREATE TABLE [MARKS]
(
    [Name] varchar(25),
    [Score] int
)
GO

--Inserting values into Mark table
INSERT INTO [MARKS]([Name], [Score]) 
    VALUES ('John',75),('Mark',75),('Steve',64),('Gates',54)
GO

--Ranking data over Score
SELECT *, DENSE_RANK() OVER (ORDER BY [Score] DESC) AS [DENSE RANK] FROM MARKS

--Dropping table
DROP TABLE [MARKS]
GO

ROW NUMBER

Row Number don’t worry much about ranking it just give an incremental number to row. It’s simple.

Name Marks Row Number
John 75 1
Mark 75 2
Steve 64 3
Gates 54 4

 

USE [tempdb]
GO

--Creating table Marks
CREATE TABLE [MARKS]
(
    [Name] varchar(25),
    [Score] int
)
GO

--Inserting values into Mark table
INSERT INTO [MARKS]([Name], [Score]) 
    VALUES ('John',75),('Mark',75),('Steve',64),('Gates',54)
GO

--Numbering data over Score
SELECT *, ROW_NUMBER() OVER (ORDER BY [Score] DESC) AS [ROW NUMBER] FROM MARKS

--Dropping table
DROP TABLE [MARKS]
GO

NTile

NTile will separate your recode into the number that you parse. If you want to group your results into 3 groups you can use NTILE(3). If you want to divide your results into 7 you can use NTILE(7).

Name Mark NTile(3)
Dedunu 89 1
Dhananjaya 85 1
Hasitha 75 2
Shamil 74 2
Sanjana 65 3
Anuradha 55 3

 

USE [tempdb]
GO

--Creating table Marks
CREATE TABLE [MARKS]
(
    [Name] varchar(25),
    [Score] int
)
GO

--Inserting values into Mark table
INSERT INTO [MARKS]([Name], [Score]) 
    VALUES ('Dedunu',89),('Dhananjaya',85),('Hasitha',75),('Shamil',74),('Sanjana',65),('Anuradha',55)
GO

--NTile(3) over Score
SELECT *, NTILE(3) OVER (ORDER BY [Score] DESC) AS [NTile(3)] FROM MARKS

--Dropping table
DROP TABLE [MARKS]
GO

If you are going to group them into 4, you will have this result. It always try to round to the lower numbers.

Name Mark NTile(4)
Dedunu 89 1
Dhananjaya 85 1
Hasitha 75 2
Shamil 74 2
Sanjana 65 3
Anuradha 55 4

 

USE [tempdb]
GO

--Creating table Marks
CREATE TABLE [MARKS]
(
    [Name] varchar(25),
    [Score] int
)
GO

--Inserting values into Mark table
INSERT INTO [MARKS]([Name], [Score]) 
    VALUES ('Dedunu',89),('Dhananjaya',85),('Hasitha',75),('Shamil',74),('Sanjana',65),('Anuradha',55)
GO

--NTile(4) over Score
SELECT *, NTILE(4) OVER (ORDER BY [Score] DESC) AS [NTile(4)] FROM MARKS

--Dropping table
DROP TABLE [MARKS]
GO

Then if you are going to group odd numbered records by even or even numbered records by odd, first tile will have an extra record.

Name Mark NTile(5)
Dedunu 89 1
Dhananjaya 85 1
Hasitha 75 2
Shamil 74 3
Sanjana 65 4
Anuradha 55 5

 

USE [tempdb]
GO

--Creating table Marks
CREATE TABLE [MARKS]
(
    [Name] varchar(25),
    [Score] int
)
GO

--Inserting values into Mark table
INSERT INTO [MARKS]([Name], [Score]) 
    VALUES ('Dedunu',89),('Dhananjaya',85),('Hasitha',75),('Shamil',74),('Sanjana',65),('Anuradha',55)
GO

--NTile(5) over Score
SELECT *, NTILE(5) OVER (ORDER BY [Score] DESC) AS [NTile(5)] FROM MARKS

--Dropping table
DROP TABLE [MARKS]
GO

If you are trying to group records by more than records number or same number it will act like ROW_NUMBER function. 

Name Mark NTile(>=6)
Dedunu 89 1
Dhananjaya 85 2
Hasitha 75 3
Shamil 74 4
Sanjana 65 5
Anuradha 55 6

 

USE [tempdb]
GO

--Creating table Marks
CREATE TABLE [MARKS]
(
    [Name] varchar(25),
    [Score] int
)
GO

--Inserting values into Mark table
INSERT INTO [MARKS]([Name], [Score]) 
    VALUES ('Dedunu',89),('Dhananjaya',85),('Hasitha',75),('Shamil',74),('Sanjana',65),('Anuradha',55)
GO

--NTile(6) data over Score
SELECT *, NTILE(6) OVER (ORDER BY [Score] DESC) AS [NTile(6)] FROM MARKS

--Dropping table
DROP TABLE [MARKS]
GO

 

USE [tempdb]
GO

--Creating table Marks
CREATE TABLE [MARKS]
(
    [Name] varchar(25),
    [Score] int
)
GO

--Inserting values into Mark table
INSERT INTO [MARKS]([Name], [Score]) 
    VALUES ('Dedunu',89),('Dhananjaya',85),('Hasitha',75),('Shamil',74),('Sanjana',65),('Anuradha',55)
GO

--NTile(8) over Score
SELECT *, NTILE(8) OVER (ORDER BY [Score] DESC) AS [NTile(8)] FROM MARKS

--Dropping table
DROP TABLE [MARKS]
GO

PARTITION BY

Lets move to Partition By clause. If you want to categorize your ranks into any other factor, you can use PARTITION BY clause. It lets you to have separate rankings on a factor that you want.

Name Subject Mark Rank
Hasitha Eng 75 1
Anuradha Eng 55 2
Dedunu Math 89 1
Dhananjaya Math 85 2
Sanjana Math 65 3
Shamil Sci 74 1

 

USE [tempdb]
GO

--Creating table Marks
CREATE TABLE [MARKS]
(
    [Name] varchar(25),
    [Subject] varchar(25),
    [Score] int
)
GO

--Inserting values into Mark table
INSERT INTO [MARKS]([Name], [Subject], [Score]) 
    VALUES ('Dedunu','Math',89),('Dhananjaya','Math',85),('Hasitha','Eng',75),('Shamil','Sci',74),('Sanjana','Math',65),('Anuradha','Eng',55)
GO

--Partitioning rank by Suject, and ranking over Score
SELECT *, RANK() OVER (PARTITION BY [Subject] ORDER BY [Score] DESC) AS [RANK] FROM MARKS

--Dropping table
DROP TABLE [MARKS]
GO

Delete using another Table

In this example I create a temp table on AdventureWorks2012 Database, and first I load all the data in HumanResources.Employee to the temp table. Then I delete data using HumanResources.Employee. This command will delete all the records in Tmp which has Gender = ‘M’ in HumanResources.Employee table.

USE [AdventureWorks2012]
GO

--Inserting data from HumanResources.Employee and Person.Person to non existing table
SELECT  *
    INTO Tmp
    FROM [HumanResources].[Employee] 
GO

--DELETE Using another table
DELETE Tmp 
    FROM [HumanResources].[Employee] AS HE, Tmp
    WHERE Tmp.BusinessEntityID = HE.BusinessEntityID
    AND HE.Gender = 'M'

--Retrieving data
SELECT * FROM Tmp
GO

--Dropping Table
DROP TABLE Tmp
GO

You can use inner or outer join to combine those data.

USE [AdventureWorks2012]
GO

--Inserting data from HumanResources.Employee and Person.Person to non existing table
SELECT  *
    INTO Tmp
    FROM [HumanResources].[Employee] 
GO

--DELETE Using another table
DELETE Tmp 
    FROM [HumanResources].[Employee] AS HE
    INNER JOIN Tmp
    ON Tmp.BusinessEntityID = HE.BusinessEntityID
    WHERE HE.Gender = 'M'

--Retrieving data
SELECT * FROM Tmp
GO

--Dropping Table
DROP TABLE Tmp
GO

Both of those statements gives same results. It will delete all the male employees.