Skip to main content

Posts

Showing posts from 2012

Training Kit (Exam 70-461) Querying Microsoft SQL Server 2012

I wrote about Programming SQL Server 2012 book before few months. But that book was not for beginners, it was little bit advance and not easy to catch the things from scratch. But Querying Microsoft SQL Server 2012 book is a good book for a beginner. I was waiting for this book’s release. Because I wanted to know the things that covers the 70-461 exam. Authors covers important points and highlight them. This kit also consist of training resources such as questions. It helps you to face the exam with out any fear.Every lesson comes with a new thing to me. And organization of this book is perfect. This book covers all the fundamentals in T-SQL. For novice DevDBA’s this would be the best book to know the capabilities of T-SQL. This book also covers new features best practices as well as bad practices. Exam 70-461 is essential exam if you are looking for Microsoft SQL Server New Certifications. This book will definitely help you to pass this exam. And this covers standard methods of codin…

Ethics of Big Data

Everybody talks how to handle big data? and What are the technologies we are going to go with? What are the technologies that we are going to drop when we move towards the big data? What are the concepts that we put away when we move to big data? I even had such a problems in my mind. And I had asked them from some guys even. But I never thought what is the ethical background we need with big data. 
What are the disciplines that we need when we are moving forward to big data? This book reveals a aspect that technical guys usually don't think a lot much. Nobody know who use our data on social sites like facebook. This book is written with so many references. Basically with proofs. It is more like a encyclopedia on Ethics of Big Data. But to read and understand this book you need some experience with Information Technology.
In the perspective of business this book is very important. Because this book says how to make data more trust worthy to deliver to business. You will have just…

Titan with Cassandra

In a previous blog post about Titan I mentioned about Titan supports three different storage back ends. For this you need Titan and Cassandra both.
Download Cassandra HereDownload Titan Here Then extract Cassandra and Titan both into a folder and take a terminal or a command prompt. Go to Cassandra bin and run cassandra.bat or cassandra. Before this you have to set JAVA_HOME.

Linux:
bash cassandra
Windows:
cassandra.bat
Then take gremlin from titan bin.

Linux:
bash gremlin.sh Windows:
gremlin.bat Then you have to configure storage backend. for that use following commands on gremlin.

cnf = new BaseConfiguration();
cnf.setProperty('storage.backend','cassandra');
cnf.setProperty('storage.hostname','127.0.0.1');
g = TitanFactory.open(cnf);
Now rest is your graph database experience!!!

Getting Started with Titan Graph Database

I have used Neo4J as my first graph database. And it had web console which makes more easier to handle. But this time I'm not going to tell you how to getting started with Neo4J. This time we are going to work with Titan which is an open source project which is held by Aurelius.

This database is more scalable graph database. Lets get to know how to get started with Titan Graph Database.

First download Titan from Titan Site
Click Here to download Titan Graph Database

Then extract titan to a disk. In this example I take D drive. If you are Linux user extract this Titan file to somewhere you can execute.

Then take a shell in Linux or command prompt in Windows. Then move to titan folder. Then move to bin.

Before take Gremlin Console you need to install Java.

After that if you are Linux guy run this command.
bash gremlin.sh Or if you are windows guy you command is,
 gremlin.bat
Now you are having gremlin. Now we are going to connect to graph database. Basically Titan we can use with thre…

Windows 8 and Dedunu

I posted blog posts about Windows 8 Developer Preview and Consumer Preview. And now I'm using Windows 8 Enterprise Edition on my sweet heart laptop. Not like Windows 7, Windows 8 is light weight. And easy to use for me because I used Windows 8 from Developer Preview onwards. But some of new users get lost with Metro-UI.

Although some of geeks say that's not good. I think, I feel its cool and good. Because desktop users expect ease of use. Microsoft had worked on giving comfortable user experience. And Windows 8 RTM don't have Aero feature. They have decided to remove that because It makes computer slow.

And Windows 8 has only three editions they are Windows 8 RT, Windows 8 Pro, Windows 8 Enterprise. In Windows 8 RT there is nice things. It is office but it's not a full version. Somehow I recommend Windows 8 to home users!!!

Every year I have some major change

Every year I'm having a problem with host or domain or anything related to my blog. Some times I changed my domain. Sometimes I changed the style of blogging. So many new things happened to my blog.

But most of them make it more cool. Now I'm only blogging on IT things. And most of the times they are related to Database and this time I hope this change will last for long time!

Enjoy my blogging!!!

What's new in MongoDB Shell 2.2.2

I really suffered while using MongoDB Shell because auto completing was not there. I was very hard to type collection and database names correctly. I wished IntelliSense to get into there. I'm tough Microsoft User. Because of that I love IntelliSense a lot. ;)

And there was no any other solution to me to make done MongoDB things easier.  Because available MongoDB GUI's are not that much good. I feel I'm in 90's when I'm using them. So after a month I got addicted to Shells. Not Only to mongo shell I practiced PowerShell also.



Now what I wished is arrived in a different form. It's auto completing. And it made me comfortable because feeling is same like using PowerShell. And also It suggest possible commands and functions. Isn't it awesome? It makes easier to learn MongoDB. Somehow now MongoDB is having visual differences!

Go Mongo!!!

How to shutdown MongoDB instance from MongoDB Shell?

For some cases we may have access to database instance of MongoDB as DBA. But we may not have access to Linux or Windows box to shutdown the MongoDB service or MongoDB instance. To Shutdown the MongoDB instance you should be able to log in to "admin" database.  If your MongoDB instance is running with "auth" mode you really need a password and username. In this demo I assume MongoDB instance is not running with "auth" mode.
I login to MongoDB shell first.
mongo
Then I change the database to admin database.
use admin
Now I'm going to run that command. This command will shutdown the instance hence think twice before you use it.
db.runCommand( { shutdown : 1 } );
That is like adventure to a DBA because your database will crash if you don't have any replication plan. ;)

How to copy a Database from a MongoDB instance to another?

Some times we need to take backups or we need to copy databases to another servers for administrative purposes. But sometimes just copying files is not enough. In MongoDB Shell they support to copy database from remote instance to current one with a single command. ( :D Just like Single Click in Windows )

For this demo I made alive two instances of MongoDB from following commands.
//Instance 1
mongod --port 9990 --dbpath /data/db1
//Instance 2
mongod --port 9991 --dbpath /data/db2
In instance 1 there is a database called "csampledb1". For that I need to connect to first instance to create that database.
mongo localhost:9990


After that with following commands I create a database with one collection.
use csampledb1
db.csamplecol1.save({id:1, name:"sample name"})
Then I log in to next MongoDB instance using MongoDB Shell.
mongo localhost:9991
Then I use single command to copy the database from instance 1 to instance 2.
db.copyDatabase("csampledb1","csampledb2",&…

MongoShell Database Navigation

If you are new to MongoDB you may need to discover the databases and collections (basically objects) on your MongoDB instance. For this easily you can use a GUI tool like MongoVUE. But in this blog post I’m not going to describe about GUI tools. I’m going to explain about MongoShell to navigate through database objects.I can remember the first day that I used Linux. In that day I fed up with Terminal and gave it up. But now I think its cool!. Some how if you want to do some thing easier I still recommend GUI Tools .First to execute those commands you should log into MongoShell. In windows mongo.exe.How to take database List?> show dbsHow to check the database that you are currently using?> dbor> print ( db );How to change to a new database?> use <database name>E.g:- > use AdventureWorks2012How to take the list of Collection in current Database?> show collectionsor> db.getCollectionNames();How to take the list of Users in Database?> show usersor > db.s…

How to take list of databases in SQL Server

In SQLCMD and Powershell I wanted to take the list of databases. In MySQL “show databases” command was there. But in SQLCMD I was unable to find such a command.--Stored Procedure
EXEC sp_databasesGO --SELECT Statement
SELECT Name FROM master.dbo.sysdatabasesGO
You can use above commands in SSMS (if you are lazy to move your mouse to object explorer) and SQLCMD. Also you can use same thing on PowerShell too.--Stored Procedure
Invoke-SQLCMD "EXEC sp_databases"

--SELECT Statement
Invoke-SQLCMD "SELECT Name FROM master.dbo.sysdatabases"
But in SQLPS you can go to your SQL Server instance’s database and just type “dir”.

I want nice output from Mongo Shell

If you have so many documents in your collection in MongoDB database. Sometimes you may want to retrieve few documents. But if we run find() we cant have nice output. You may need to have nice indentation and line breaks. You may want results in more readable way. Then you can use pretty().Who can read this??If you use pretty() you can take nice output with Mongo Shell.

Classic Cryptography

Secret Messages!!I was always amazed by secret messages and Cryptography is the science applied for this. Modern cryptography has gone to a high standard as it's bonded with mathematics, computer science and electrical engineering. I thought of giving a basic idea about classic cryptography for a change.
There are three methods in classic cryptography.concealed messages (Steganography) - These hidden messages are written in invisible ink,microdots etc. Codes - Here words or phrases are represented by predetermined words,numbers or symbols which were included in code books. Ciphers - I feel this as the most challenging method in classic cryptography. In this context individual letters are disguised using various techniques.Basically this is divided in to 2.
-> Substitution ciphers
->  Transposition ciphers
I'll give a brief idea about these 2 techniques. Substitution ciphers
Here letters in the message can be replaced with numbers,other letters,symbols or combinations of all…

How to install MongoDB as a Windows Service

At the first date I heard about NoSQL DBMS, I felt insane. Then I got an eager to learn. But I didn’t pay any attention to learn. Later I got to know that MongoDB is a scalable one. Then I got a chance to follow free MongoDB course. Although I registered to both Developer and DBA courses I had no time to watch developer tutorials. But I finished DBA 1st Week. In that video tutorial they explain how to install MongoDB on Windows as well as on UNIX. But when we are using Mongo in Windows Development Environment it’s a headache to start service again and again. Because of that I wanted to install MongoDB as a Windows Service.

In that video tutorial they explain how to just run MongoDB on Windows Environment. First of all you should have a data directory. For that you should create two folders in the drive that you are planing to install MongoDB. Lets assume that we are going to install MongoDB on D: Drive.

Then you should create two directories(Folders) in D: drive


D:\data

D:\data\db

You can …

Microsoft SQL Server පටන් ගමු!!

මම කලින් දාපු පෝස්ට් වල තියන SQL ස්ටේට්මන්ට් රන් කරලා පුරුදු වෙලා බලන්න විදිහක් මට පෝස්ට් කරන්න බැරි වුනා. ඇත්තටම සරල SQL ස්ටේට්මන්ට්ස් හැම එකක්ම වගේ ඕනම ඩේටාබේස් මැනෙජ්මන්ට් සිස්ටම් එකක රන් කරලා බලන්න පුලුවන් (Oracle DB, SQL Server, Access, MySQL). එත් මම පාවිච්චි කරන්නේ SQL Server ඉතින් මම හිතුවා SQL Server ඉගෙන ගන්න කැමති අයට ඒකට අවශ්‍ය දේවල් ඩව්න්ලෝඩ් කරගන්න තැන් පෝස්ට් කරන්න.http://www.microsoft.com/en-us/download/details.aspx?id=29062ඉහත දීලා තියන ලින්ක් එකෙන් ගිහින් ENU\x64\SQLEXPRWT_x64_ENU.exe හරි ENU\x86\SQLEXPRWT_x86_ENU.exe ඩව්න්ලෝඩ් කරගෙන ඉන්ස්ටෝල් කරගන්න. ඇත්තටම ඉන්ස්ටෝල් කරන එක එච්චර අමාරු නෑ. ඒවගේමයි මේ එඩිෂන් එක ඉගෙන ගන්න අයටම වෙන් කරපු එකක් ඒක හන්දා ලයිසන් ප්‍රශ්නයකුත් නෑ. තමුන්ගේ මෙහෙයුම් පද්ධතියට ගැලපෙන විදිහට 64බිට් හරි 32 බිට් හරි ඩව්න්ලෝඩ් කරගෙන ඉන්ස්ටෝල් කරගන්න. මේකේ ඇත්තටම වැදගත්ම දේ SQL Server Management Studio Express එක SSMS එකෙන් ලේසියෙන්ම අවශ්‍ය Queries ලියන්න පුලුවන්.ඊට පස්සේ SQL Server ඉගෙන ගන්නවානම් ගොඩක් වැදගත් වන දෙයක් තමා සාම්පල් ඩේටාබේස් එක. ඇත්තටම…

SSLUG October 2012

Although it was a rainy day so many attendees were there to participate SQL Server Universe Monthly User Group Meeting. At the beginning Dinesh’s TV interview was on the screen until the meeting begins. And then we had 3 sessions one on Data migration to Dynamics, one on execution plan myths, and last one was about Paging Results in SQL Server.If you never knew about SQLServerUniverse User Group Meeting Please go to the site http://sqlserveruniverse.com/ and follow on twitter, linkedin and facebook. Don’t forget to register on the site. Usually it happens on 3rd Wednesday on every month.

SELECT–SQL

SELECT ස්ටේට්මන්ට් එක ඩිවලොපර්ලා ඇතුලු හැමෝටම වැදගත් වෙන ස්ටේට්මන්ට් එකක්. වැඩියෙන්ම භාවිතා වෙන ස්ටේට්මන්ට් එක බොහෝ වෙලාවට මේක වෙන්නත් පුලුවන්. අපට අවශ්‍ය විදිහට ඩේටාබේස් එකේ ටේබල් කිහිපයකින්ම දත්ත ගැනීමට මේ ස්ටේට්මන්ට් වලට පුලුවන්. ඒවගේම ගණිත කර්ම කරන්නත් පුලුවන්.උදාහරණයන් සදහා මම පහල තියන tblMarks ටේබල් එක දිගටම පාවිච්චි කරනවා.IDNameMarks1Dedunu672Satheeq873Dinesh984Shamil675Hasitha876Abinandana76අපි හිතමු අපිට අවශ්‍යයි කියලා තියන දත්ත ඔක්කොම ටේබල් ගන්න අවශ්‍යයි කියලා. අපි පාවිච්චි කරන්න ඕන ස්ටේට්මන්ට් එක මේ වගේ එකක්. SELECT * FROM dbo.tblMarks
මේ ස්ටේට්මන්ට් එක රන් කලොත් අපිට ලැබෙන්නේ මුලින්ම තියන වගුව වගේම ප්‍රතිඵලයක්. අපි හිතමු දැන් අපිට අවශ්‍ය වෙලා තියනවා කියලා නම් ලැයිස්තුවක් ඩේටාබේස් එකෙන් අරගන්න. එතකොට අපි රන් කරන්න ඕනේ පහත විදිහේ ස්ටේට්මන්ට් එකක්.SELECT Name FROM dbo.tblMarks
නම් සමග ලකුනුත් අවශ්‍ය නම්  පාවිච්චි කරන්න ඕනේ මේ පහල තියන ස්ටේට්මන්ට් එක.SELECT Name, Marks FROM dbo.tblMarks



Name
Marks

Dedunu
67

Satheeq
87

Dinesh
98

Shamil
67

Hasitha
87

Abinandana
76
ලැබෙන ප්‍රතිඵලය ඉහත වගේ එකක්.  …

SQL වල කොටස්

SQL එක එක කාර්යන් වලට වෙන්කරන ලද කොටස් තියනවා. ඇත්තටම එකම එක භාෂාවක් වුනාට එක එක කොටස් තියනවා එක එක දේවල් වලට.DML - Data Manipulation Languageවැඩියෙන්ම SQL වල භාවිතා වෙන්නේ මම හිතන විදිහට DML Operation වෙන්න ඕන. මොකද Database එකක තියන දත්ත Database එකෙන් අරගන්න, Database එකේ තියන දත්ත වෙනස් කරන්න ඒවගේම දත්ත අලුතෙන් එකතු කරන්න තියන දත්ත මකන්න කියන හැමදේටම SQL වලදි අපි පාවිච්චි කරන්නේ DML.උදා:-SELECTDELETE INSERT UPDATEDDL – Data Definition LanguageDatabase වල තියන දත්ත අපි ගබඩා කරලා තියන්නේ ටේබල් වල. ඒවගේ ටේබල්, ඉන්ඩෙක්, වීවිස් වගේ දේවල් ඩේටාබේස් එකේ හදන්න වෙනස් කරන්න මකන්න වගේ දේවල් වලට DDL භාවිතා කරනවා.උදා:-CREATEDROPALTERDCL – Data Control LanguageDCL භාවිතා කරලා අපි කරන්නේ දත්ත පාවිච්චි කරන්න දත්ත මකන්න වෙනස් කරන්න එක එක පරිශීලකයන්ට අවශ්‍ය බලය ලබා දෙන්න. එවගේම දැනට තියන අයිතීන් ඉවත් කරන්න වෙනස් කරන්නත් මේ DCL භාවිතා කරනවා.උදා:-GRANTREVOKE

SQL ගැන හැදින්වීමක්!

SQL යනු දත්ත සම්බන්ධයෙන් ඇති කාර්යන් ඉටු කරගැනීමට භාවිතා කරන භාෂාවකි. 5th Generation ලැන්ග්වෙජ් එකක් විදිහට SQL සලකනවා. ඒවගේම SQL කියන එක “SEQUEL” කියලා තමයි ගොඩක් අය කියන්නේ දැන් SQL කියන්නේ Structured Query Language වුනාට මේ ලැන්ග්වෙජ් එකට ඉස්සර Structured English Query Language කියලා තමා කියලා තියෙන්නේ. පස්සේ කාලෙක මේක SQL කරලා තියනවා. ඇත්තටම SQL එක්ක වැඩ කරන්න ලේසි.SQL වලට Standard එකක් තිබුනත් සමාගම් තමුන්ගේ නිෂ්පාදන වලදි අලුත් අංග එකතු කරලා වෙනම භාෂාවල් විදිහට නම් කරනවා. Oracle පාවිච්චි කරන්නේ P-SQL එවගේම SQL Server පාවිච්චි කරන්නේ T-SQL. කොහොම වුනත් ගොඩක් වෙලාවට මූලික දේවල් එක වගේමයි.

Programming Microsoft SQL server 2012

Before few weeks I got a book from Oreilly Blogger Review program to review a book. I took enough time to read this book. This book covers most of the new trends and features in SQL Server 2012 with good explanation. I was addicted to first chapter to get to know about the SQL Server Data Tools. Because previously I was studying development things like C# and Visual Studio because of that I was not be able to find out about SQL Server Development Tools. With SQL Server 2012 SQL Server Data Tools were introduced. In first chapter completely describes about SSDT from the point of Developers and Dev DBA’s. All over the book I didn’t feel like reading a tech book. It is well organized and I felt that I read a story book. But this book is not good for absolute beginners who is looking for SQL Server Development. And this book also balanced one, It doesn’t cover only XML or CLR. Its covering most of the topics with useful things. This is like a essence for database developers who uses SQL S…

How to compare data in two tables?

In the beginning of this week I got a task from Susantha to modify a stored procedure. After the modification I had to test to compare the result sets before modification and after modification. That procedure only returns a data table. So I had so many test data on another table. Then I wrote a script to go through the script one by one. Actually I wanted to check whether those results are identical. I mean not the table structure. I wanted to perform a data comparison.
DECLARE @COUNTN INT;
DECLARE @COUNTO INT;
DECLARE @COUNTU INT;

CREATE TABLE #TEMPN
(
--Columns Here
)

CREATE TABLE #TEMPO
(
--Columns Here
)

CREATE TABLE #TEMPU
(
--Columns Here
)

INSERT INTO #TEMPN
--Select From new table

INSERT INTO #TEMPO
--Select From old table

SELECT @COUNTU = COUNT(1)
FROM #TEMPU
SELECT @COUNTN = COUNT(1)
FROM #TEMPN
SELECT @COUNTO = COUNT(1)
FROM #TEMPO

IF @COUNTN <> @COUNTO
BEGIN
SELECT *
FROM #TEMPN
SELECT *
FROM #TEMPO
PRINT 'ERROR'
END

IF @COUNTN <> @COUNTU
BE…

Easy way to analyse results

I have seen in many video tutorials they have different tabs for Editor, Results, Messages and Execution Plans. And they are streched over complete screen. Most of the times need to resize the result area to see our results. Actually its annoying me. Then I noticed in on of Paul Randal’s video he uses Separate tabs for Results editor and messages in SQL Server Management Studio (SSMS). Then I wanted to find how to get those tabs to my SSMS (Often I call it SMSS don’t know why ). 1. Open you SQL Server Management Studio (SSMS)2. Go to Tools –> Option3. Check “Display results in a separate tab” and “Switch to results tab after the query executes” both.4. Click ok and enjoy Results like this.If you don’t like to use it always you can use this option to single script by click on Query option on tool bar and follow 3rd step.

SQL Server is not releasing memory?

I got few questions to answer quickly in last week just like a test. Then I suddenly answered then after that I tried to find correct answers to them. I found answers for most of them. And one of them was there as it was. A Windows server has 32 GB of memory and dedicated for SQL Server database. Every time you start the server the memory utilization of SQL Server gradually increases, until it takes almost all the memory and remains there for days even when there is no database activity. What will you do ? Actually I answered We can set maximum memory limit. Some how gradually SQL Server uses available memory.I knew that SQL Server uses and usually doesn’t release memory, although he don’t need to use it. But I didn’t knew why is that. Then I got to know that Not releasing memory is a feature of SQL Server. On servers we don’t run our day to day applications and most of the times we have allocated separate box for SQL Server. Then nobody will use that memory. If nobody uses that why s…

Be careful if you are using CTE!

Previously I wrote a post about CTE (Common Table Expressions). And Common Table Expressions are valid for a single statement only. But single statement can use many CTEs. And statement also should be within same batch.USE AdventureWorks2012
GO

--Defining CTE
WITH ctePerson
AS
(
SELECT *
FROM Person.Person
)
--First Statement will run
SELECT FirstName FROM ctePerson
--Second statement will occur an error
SELECT FirstName FROM ctePerson

And in my previous post I have mentioned about many CTEs for one statement. And there is another important thing that is be careful when you are naming CTEs. Take a look on below example.
USE tempdb
GO

--Creating two tables
CREATE TABLE cteSampletbl1
(
id int,
name varchar(50)
)
GO

CREATE TABLE cteSampletbl2
(
id int,
name varchar(50)
)
GO

--Populating tables
INSERT INTO cteSampletbl1 VALUES
(1,'Satheeq'),
(2,'Preethi'),
(3,'Dinesh'),
(4,'Angelo')
GO

INSERT INTO cteSampletbl2 VALUES
(1,'Shamil'),
(2,'Hasitha'),
(3,'Abhinandana'),
(4,…

How varchar data is stored?

In SQL Server we use varchar, varbinary to save disk space in our tables. But its not saving data always as it sounds. When the data stores it takes few extra bytes to describe the data length. Because of that there is no gain using varchar(2). If you use varchar(2) it will take 4 or 3 bytes to store data. varchar(8000) or below should have 2Byte offset to store the variability of character count. If you are using varchar(max) is different. It should have 4 Bytes offset. But I’m still looking for that to confirm it takes 4 Bytes. And it is stored in Variable Column Offset Array.Picture: http://www.sqlservercentral.com/blogs/aschenbrenner/2011/06/29/the-mystery-of-the-null-bitmap-mask/

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.

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

t…

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.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.2. I followed the wizard n…

Common Table Expressions (CTE)

In Stored procedures and in our SQL Statement sometimes we need to use same query again and again. In that case you can use CTE.
USE AdventureWorks2012
GO

--Defining CTE
WITH Empl
AS
(
SELECT * FROM HumanResources.Employee
)

--Using CTE
SELECT Empl.* FROM Empl
GO

And you can use several CTE’s in one statement.

USE AdventureWorks2012 GO

--Defining CTE

WITH Empl AS ( SELECT * FROM HumanResources.Employee ),

Pers AS ( SELECT * FROM Person.Person )

--Using CTE

SELECT Empl.*, Pers.*

FROM Empl INNER JOIN Pers

ON Empl.BusinessEntityID = Pers.BusinessEntityID

GO

Updated : At the very begining I though that CTE were reusable again and again inside a one batch. But it was a myth. I realized it after posting this post, but forgot to remove that today I removed that.

Ranking in T-SQL

In Queries we need to rank and number records. SQL Server gives you few functions to rank your records.RANKRank we can use  to rank data in normal way. In this ranking if we have two 1 next one will have rank 3.  NameMarksRankJohn751Mark751Steve643Gates544USE [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], [Scor…