December 24, 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 coding in SQL and TSQL both. In first chapter Authors discuss about Relational Theories very well. And they point out the misconceptions about relational database management system.

I call it complete Kit of learning TSQL. But this book is not for experienced DBAs or Developers. You can buy this book from here.

December 22, 2012

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 only few pages. But ethics are compressed within those pages. If you like big data you should read this book!

December 21, 2012

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.
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 three main storage backends. They are
  • Cassandra
  • HBase
  • Oracle Berkley 
But I'm not going to use any of them now. Now I'm just going to use Titans Native Storage.
 g = TitanFactory.open('local/tmp');
 Below command will initiate a graph and assign it to g variable. Now in you bin you can see local and temp folders are created.

If you hope to search through your graph in Titan you have to index it first. Otherwise you can't search it. Actually it's a bug like thing in titan, and it's a know issue. Let say we are going to search with "name" property. Then we have to add "name" index first.

g.createKeyIndex('name', Vertex.class);

Now you are ready to add nodes and edges. And you have a index on "name" property.

v = g.addVertex(null);
This will create a new node on graph. we are going to set properties to this node.
v.setProperty('name','dedunu');
v.setProperty('type','person');
v.setProperty('age',20);
 v1 = g.addVertex(null);
v1.setProperty('name','malinda');

v1.setProperty('type','person');

v1.setProperty('age',22);
 v2 = g.addVertex(null);

v2.setProperty('name','UCSC');
v2.setProperty('type','institute');

Now we have three nodes. Two of them are Persons and the other one is a institute.  Now we are going to create relationships between those three nodes.

e1 = g.addEdge(null, v, v2, 'study in');
e2 = g.addEdge(null, v1, v2, 'study in');
e3 = g.addEdge(null, v, v1, 'knows');
e4 = g.addEdge(null, v1, v, 'knows');

Now you have a graph like this.


 Now we are going to have a journey around our beautiful data!!! And now I want to know where malinda "study in". For that I have to load Malinda to a vertex variable. I'll use existing one.

v1.out('study in').map();

Now I want who study in UCSC.  For that I have to load UCSC to a variable first. I'll use existing one.

v2.in('study in').map();

If I only need names of them then below one is the command.

v2.in('study in').name;
Finally to commit all those thing to disk you have to shutdown the graph for that run below command.

g.shutdown();

Be careful those commands are case sensitive!!! Enjoy Graphs!!!

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!!!

December 17, 2012

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.

Image

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

mongoshellinstance1

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","localhost:9990")

mongoshellinstance2

Syntax of this function is like below. There are two arguments which I didn't use.
db.copyDatabase(fromdb, todb, fromhost, username, password)

December 05, 2012

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!. Smile Some how if you want to do some thing easier I still recommend GUI Tools Winking smile.

First to execute those commands you should log into MongoShell. In windows mongo.exe.

How to take database List?

> show dbs

How to check the database that you are currently using?

> db

or

> print ( db );

How to change to a new database?

> use <database name>

E.g:-

> use AdventureWorks2012 

Winking smile

How to take the list of Collection in current Database?

> show collections

or

> db.getCollectionNames();

How to take the list of Users in Database?

> show users

or

> db.system.users.find();

November 12, 2012

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_databases

GO

 

--SELECT Statement
SELECT Name FROM master.dbo.sysdatabases

GO


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”.

November 05, 2012

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().

image

Who can read this??

image

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 these.Simple substitution cipher and multiple substitution cipher comes under this.

  • Simple substitution cipher -
    8-5-12-16
    Can you understand the cipher text above?
    It's basically the simplest substitution that can be used. Each number stands for the letters of English alphabet in alphabetical order. We can see that the deciphered text is "HELP".
    And also we can have a special cipher alphabet corresponding to the letters in the normal alphabet and encode.
  • Multiple substitution cipher -
    Message has to encode using a key word in multiple substitution.
    If we want to encode the message "Send a rescue team soon" with the key word "five" following steps should be followed.
    First match up key words with the letters of the message.s-e-n-d-a-r-e-s-c-u-e-t-e-a-m-s-o-o-n
    f   i  v e  f  i  v e  f  i  v e f   i  v  e  f   i  v

    Then follow the steps below taking the corresponding letters.
    If we take the first corresponding pair, s-19th letter and f-6th letter ->  s+f=25 -> 25th letter-y
    When it comes to the 3rd pair it's a bit different.
    n-14th letter and v-22nd letter -> n+v=36
    As 36>26, 36-26=10 take the 10th letter "j"
    Like that we have to encrypt all the letters in the message.Final cipherd text would be "ynjigaaxidaykjixuxj "

    Transposition ciphers
    In this method letters in a message are jumbled and arranged in a block according to a secret scheme.Mostly this is done in a geometric design.
    If we want to encode the message "Meet me in the usual place at 10 tonight" it can be done as below in the simplest way.
    MEETMEIN
    THEUSUAL
    PLACEAT1
    0TON IGHT
    Transposition cipher when arranged in a rectangle is called a columnar cipher.It can be encoded using a key word.
    If we want to cipher the message "I need help" following steps should be followed.
    First arrange the message in a rectangular block.
    INE
    EDH
    ELP

    Then a key word must be chosen to assign a number to each column in the rectangle.If we select the key word "CAT" each letter is assigned a column of letters as below.

    CAT
    INE
    EDH
    ELP

    As  C-3rd letter,  A-1st letter, T-20th letter ,
    the alphabetical order of the key word will be A,C,T.
    So when encoding we have to take the column under A first, then the column under C,finally the column under T.
    It'll give the final output,
    NIE
    DEH
    LEP

    These techniques can be developed in more complex ways too though I gave simple examples. Hope you got a basic idea about the techniques that were used in classic cryptography.


October 30, 2012

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 manually change data path but for a beginner its better to use default path. And if you are trying to install it as Service you need to create a log folder.


  • D:\log


Then open a command prompt and go to the MongoDB bin. I assume you have pasted it on D:\ drive. your directory path will look like this D:\mongodb-win32-x86_64-2.2.0\bin.

After that you should run mongod.exe to install MongoDB as a windows service.

mongod.exe --install --logpath D:\log


When you have type and enter this command on CMD. It will create a Service for MongoDB. You can start MongoDB service with below command.

net start MongoDB


Enjoy Mongo!!!!

October 24, 2012

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 ඉගෙන ගන්නවානම් ගොඩක් වැදගත් වන දෙයක් තමා සාම්පල් ඩේටාබේස් එක. ඇත්තටම AdventureWorks කියලා ගොඩක් ප්‍රසිද්ද සාම්පල් ඩේටාබේස් එකක් තියනවා. ඒක හැමෝම පාවිච්චි කරනවා. ඉතින් ඒකත් ඩව්න්ලෝඩ් කරගෙන SQL Server එකට ඇටැජ් කරගත්ත නම් SQL Server ඉගෙන ගන්න ගොඩක් ලේසි.

http://msftdbprodsamples.codeplex.com/releases/view/55330

ගිහින් පුලුවන් ඒ සාම්පල් ඩේටාබෙස් එක ඩව්න්ලෝඩ් කරගන්න. AdventureWorks2012 Data File කියන එක ඩව්න්ලෝඩ් කරගත්තා නම් ලේසි. නැත්නම් AdventureWorks2012-Full Database Backup ඩව්න්ලෝඩ් කරගෙන රිස්ටෝර් කරන්නත් පුලුවන්

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.

_DSC5732

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.

Three Speeketeers

SELECT–SQL

SELECT ස්ටේට්මන්ට් එක ඩිවලොපර්ලා ඇතුලු හැමෝටම වැදගත් වෙන ස්ටේට්මන්ට් එකක්. වැඩියෙන්ම භාවිතා වෙන ස්ටේට්මන්ට් එක බොහෝ වෙලාවට මේක වෙන්නත් පුලුවන්. අපට අවශ්‍ය විදිහට ඩේටාබේස් එකේ ටේබල් කිහිපයකින්ම දත්ත ගැනීමට මේ ස්ටේට්මන්ට් වලට පුලුවන්. ඒවගේම ගණිත කර්ම කරන්නත් පුලුවන්.

උදාහරණයන් සදහා මම පහල තියන tblMarks ටේබල් එක දිගටම පාවිච්චි කරනවා.

ID Name Marks
1 Dedunu 67
2 Satheeq 87
3 Dinesh 98
4 Shamil 67
5 Hasitha 87
6 Abinandana 76

අපි හිතමු අපිට අවශ්‍යයි කියලා තියන දත්ත ඔක්කොම ටේබල් ගන්න අවශ්‍යයි කියලා. අපි පාවිච්චි කරන්න ඕන ස්ටේට්මන්ට් එක මේ වගේ එකක්.

SELECT * FROM dbo.tblMarks

මේ ස්ටේට්මන්ට් එක රන් කලොත් අපිට ලැබෙන්නේ මුලින්ම තියන වගුව වගේම ප්‍රතිඵලයක්. අපි හිතමු දැන් අපිට අවශ්‍ය වෙලා තියනවා කියලා නම් ලැයිස්තුවක් ඩේටාබේස් එකෙන් අරගන්න. එතකොට අපි රන් කරන්න ඕනේ පහත විදිහේ ස්ටේට්මන්ට් එකක්.

SELECT Name FROM dbo.tblMarks

නම් සමග ලකුනුත් අවශ්‍ය නම්  පාවිච්චි කරන්න ඕනේ මේ පහල තියන ස්ටේට්මන්ට් එක.

SELECT Name, Marks FROM dbo.tblMarks























NameMarks
Dedunu67
Satheeq87
Dinesh98
Shamil67
Hasitha87
Abinandana76

ලැබෙන ප්‍රතිඵලය ඉහත වගේ එකක්.  අපි හිතමු කාටහරි අවශ්‍ය වෙනවා නම් ලැයිතුව අකාරාදි පිලිවෙලට අරගන්න. එතකොට රන් කරන්න අවශ්‍ය පහත ස්ටේට්මන්ට් එක.

SELECT Name 
FROM dbo.tblMarks
ORDER BY Name ASC

ස්ටේට්මන්ට් අතර අලුතෙන් ලයින් කොච්චර තිබුනත් ගැටලුවක් වෙන්නේ නැ. පහත ආකාරයට තමයි ඉහත තියන ස්ටේට්මන්ට් එකේ රිසාල්ට් සෙට් එක ලැබෙන්නේ.

















Name
Abinandana
Dedunu
Dinesh
Hasitha
Satheeq
Shamil

අපි හිතමු ඒවගේම අපිට අවශ්‍ය වෙනවා ලකුනු වැඩියෙන්ම ගත්ත කෙනාගේ නමත් ලකුනුත් අරගන්න. ඒ සදහා අපිට පහත තියන ස්ටේට්මන්ට් එක පාවිච්චි කරන්න පුලුවන්.

SELECT Name, Marks
FROM dbo.tblMarks
ORDER BY Marks DESC

එතකොට ඇත්තටම ලැබෙන්නේ පහත ආකාරයේ රිසාල්ට් සෙට් එකක්.
























NameMarks
Dinesh98
Satheeq87
Hasitha87
Abinandana76
Dedunu67
Shamil67

SQL වල කොටස්

SQL එක එක කාර්යන් වලට වෙන්කරන ලද කොටස් තියනවා. ඇත්තටම එකම එක භාෂාවක් වුනාට එක එක කොටස් තියනවා එක එක දේවල් වලට.

DML - Data Manipulation Language

වැඩියෙන්ම SQL වල භාවිතා වෙන්නේ මම හිතන විදිහට DML Operation වෙන්න ඕන. මොකද Database එකක තියන දත්ත Database එකෙන් අරගන්න, Database එකේ තියන දත්ත වෙනස් කරන්න ඒවගේම දත්ත අලුතෙන් එකතු කරන්න තියන දත්ත මකන්න කියන හැමදේටම SQL වලදි අපි පාවිච්චි කරන්නේ DML.

උදා:-

  • SELECT
  • DELETE
  • INSERT
  • UPDATE

DDL – Data Definition Language

Database වල තියන දත්ත අපි ගබඩා කරලා තියන්නේ ටේබල් වල. ඒවගේ ටේබල්, ඉන්ඩෙක්, වීවිස් වගේ දේවල් ඩේටාබේස් එකේ හදන්න වෙනස් කරන්න මකන්න වගේ දේවල් වලට DDL භාවිතා කරනවා.

උදා:-

  • CREATE
  • DROP
  • ALTER

DCL – Data Control Language

DCL භාවිතා කරලා අපි කරන්නේ දත්ත පාවිච්චි කරන්න දත්ත මකන්න වෙනස් කරන්න එක එක පරිශීලකයන්ට අවශ්‍ය බලය ලබා දෙන්න. එවගේම දැනට තියන අයිතීන් ඉවත් කරන්න වෙනස් කරන්නත් මේ DCL භාවිතා කරනවා.

උදා:-

  • GRANT
  • REVOKE

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. කොහොම වුනත් ගොඩක් වෙලාවට මූලික දේවල් එක වගේමයි.

October 22, 2012

Programming Microsoft SQL server 2012

catBefore 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 Server 2012 or who is planning to use SQL Server 2012.

Each chapter had lot of things to learn. Actually this is a good book. Read it if you are a dev DBA in SQL Server 2012!!! Programming Microsoft SQL Server 2012 was a pretty interesting book for me.

October 17, 2012

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
BEGIN
SELECT
*
FROM #TEMPN
SELECT *
FROM #TEMPO
PRINT 'ERROR'
END

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

DROP TABLE
#TEMPN
DROP TABLE #TEMPO
DROP TABLE #TEMPU


This will check whether your data in two tables are identical or not. if there’s any mismatch it will print error and show the both result sets. And there’s another issue on this script if one table returns NULL and the other table returns empty table it will take it as matched. Then I corrected it again.

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
BEGIN
SELECT
*
FROM #TEMPN
SELECT *
FROM #TEMPO
PRINT 'ERROR'
END

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

--New Code Block here

IF EXISTS(#TEMPO)
BEGIN
IF
NOT EXISTS(#TEMPN)
BEGIN
SELECT
*
FROM #TEMPN
SELECT *
FROM #TEMPO
PRINT 'ERROR'
END
END


IF
EXISTS(#TEMPN)
BEGIN
IF
NOT EXISTS(#TEMPO)
BEGIN
SELECT
*
FROM #TEMPN
SELECT *
FROM #TEMPO
PRINT 'ERROR'
END
END



DROP TABLE
#TEMPN
DROP TABLE #TEMPO
DROP TABLE #TEMPU

October 12, 2012

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 Winking smile).

1. Open you SQL Server Management Studio (SSMS)

2. Go to Tools –> Option

3. Check “Display results in a separate tab” and “Switch to results tab after the query executes” both.

image

4. Click ok and enjoy Results like this.

image

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.

image

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 should we release and allocate again and again. When we are discussing about this problem our production server had reached its maximum memory too Winking smile. And this is not our production servers Smile.

sql server memory leak

Preethi told that this happens often. People who don’t know about SQL Server behavior when saw “task manager” like above they are suggesting to double the memory of server. Then until it takes few days they are happy. Again they are having same issue. Sad smile. Actually it’s not a problem with SQL Server.

If you really want to release that memory you can easily restart SQL Server. Then it begins everything from the beginning. Other wise you can execute those commands.

But even in the TechNet they haven’t mentioned that it is recommended running those commands against production servers. Somehow as I think theirs no need to flush memory manually. Because if you are using SQL Server on your laptop, every time that you restart your laptop it will flush again and again. In production servers we don’t need to flush it manually. Let SQL Server to use that memory as he want.

If you are using one box to install SQL Server and Application both you can set maximum memory limit to SQL Server. Then it will not exceed that limit. But I found this thread from SQLServerCentral.

http://www.sqlservercentral.com/Forums/Topic982342-1550-1.aspx

In that thread he is saying that SQL Server exceeds that maximum memory limit. But technically it should not happen. And I haven’t tested it also. But there may be a reason for that too. If you are setting maximum memory limit you should restart SQL Server. If you don’t restart it, it uses previous memory limit. Somehow below link says another thing. I have to learn about it further. Smile

Yup. Perfectly normal.
Max server memory is the max size of the buffer pool, the memory area that contains the data cache, plan cache and a whole bunch of other caches. SQL also uses memory outside the buffer pool for things like backup buffers, thread stack, linked server drivers, CLR and a few other things. This is outside of the buffer pool, so it's not part of 'max server memory'
On 32-bit SQL, that's referred to as MemToLeave (memory to leave unallocated when assigning the buffer pool). On 64 bit that term has no meaning.

http://www.sqlservercentral.com/Forums/Topic1197388-1550-1.aspx

Picture: http://piglings.blogspot.com/2009/10/sql-server-2008-memory-leak.html

October 04, 2012

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,'Susantha')
GO

/*
This CTE is defined with existing table name without any error
*/
WITH cteSampletbl2
AS(
SELECT * FROM cteSampletbl1
)
--This statement uses CTE
SELECT * FROM cteSampletbl2
--This statement uses existing table
SELECT * FROM cteSampletbl2

--Dropping tables
DROP TABLE cteSampletbl1
DROP TABLE cteSampletbl2
GO

Below example it lets us to create a CTE with a name of existing table without any warnings or errors. And it gives priority to CTE in first statement. Then it uses existing table in second statement. So it would be a best practice to use a separate prefix for CTEs. Then take a look into next sample which is taken from Dinesh’s presentation for SQL Server Universe Group.


USE tempdb
GO

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

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

--This CTE got error
WITH cteSampletbl1
AS(
--Same name could not be used as name and in CTE
SELECT * FROM cteSampletbl1
)
SELECT * FROM cteSampletbl1

--This CTE don’t have any error
WITH cteSampletbl1
AS(
--full name of object should provide
SELECT * FROM dbo.cteSampletbl1
)
SELECT * FROM cteSampletbl1

--Dropping tables
DROP TABLE cteSampletbl1
GO

You can’t use same table name as the name of the CTE and inside the CTE.

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.

062911_1840_Themysteryo1

Picture: http://www.sqlservercentral.com/blogs/aschenbrenner/2011/06/29/the-mystery-of-the-null-bitmap-mask/

October 03, 2012

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.

October 02, 2012

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 NameLengthPrecisionScaleNullable
bigint8190Y
bit11NullY
decimal173838Y
int4100Y
money8194Y
numeric173838Y
smallint250Y
smallmoney4104Y
tinyint130Y

Approximate Numerics






















Type NameLengthPrecisionScaleNullable
float853NullY
real424NullY

Date and Time














































Type NameLengthPrecisionScaleNullable
date3100Y
datetime28277Y
datetime8233Y
datetimeoffset10347Y
smalldatetime4160Y
time5167Y

Binary Strings

 



























Type NameLengthPrecisionScaleNullable
binary80008000NullY
image162147483647NullY
varbinary80008000NullY

Character Strings




















Type NameLengthPrecision
char80008000
varchar80008000
text162147483647

Unicode Character Strings




















Type NameLengthPrecision
nchar80004000
nvarchar80004000
ntext161073741823

Other Data Types


































Type NameLengthPrecisionNullable
hierarchyid892892Y
sql_variant80160Y
timestamp88N
uniqueidentifier1616Y
xml-1-1Y

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.

October 01, 2012

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

September 25, 2012

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.

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.























NameMarksDense Rank
John751
Mark751
Steve642
Gates543

 

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.























NameMarksRow Number
John751
Mark752
Steve643
Gates544

 

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).































NameMarkNTile(3)
Dedunu891
Dhananjaya851
Hasitha752
Shamil742
Sanjana653
Anuradha553

 

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.































NameMarkNTile(4)
Dedunu891
Dhananjaya851
Hasitha752
Shamil742
Sanjana653
Anuradha554

 

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.































NameMarkNTile(5)
Dedunu891
Dhananjaya851
Hasitha752
Shamil743
Sanjana654
Anuradha555

 

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. 































NameMarkNTile(>=6)
Dedunu891
Dhananjaya852
Hasitha753
Shamil744
Sanjana655
Anuradha556

 

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.






































NameSubjectMarkRank
HasithaEng751
AnuradhaEng552
DedunuMath891
DhananjayaMath852
SanjanaMath653
ShamilSci741

 

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