An interesting way of aliasing TIP#107

I recently gone through something and found a unique way of aliasing. I thought it must be share so other techies also aware of it (or might be you already aware of it).

See below example

SELECT *
FROM (VALUES (‘Rajat’,30),
             (‘Sandeep’,40),
             (‘Sunil’,35),
             (‘Shreya’,50),
             (‘Virendra’,45)) AS T(Name,Runs)

aliasing

Now when you it  you will get following result

Aliasing_result

I hope you may like this tips.

Enjoy!!!

RJ!!!

Advertisements

Sequence feature TIP #76

Although it is a old feature for those who knows ORACLE but for SQL server developers it is a new feature.

Let understand it by an example. Suppose we want an auto incremented column a part from primary key which is a identity column,

then to achieve this we can use sequence feature.

We can create  sequence feature by following command

Sequence

“CREATE SEQUENCE StudentEnrollmentId AS INT
START WITH 2014000
INCREMENT BY 1”

so if you see above statement we have created a sequence with name StudentEnrollmentId which is an integer type sequence and first value means starting point is 2014000 and each time when we call sequence it will be incremented by 1.

We can create same sequence by screen also as shown in below figure

SequenceView

We have other option also  as shown in below

CREATE SEQUENCE SEQUENCE_NAME
AS DATA_TYPE
START WITH <constant>
INCREMENT BY <constant>
MINVALUE value
MAXVALUE value
CYCLE | NO CYCLE
CACHE int | NO CACHE

as shown in above option we can provide minimum & maximum for sequence. We have cycle option mean restart again after reaching maximum or minimum.

Now we can use it with following way

Sequence_1

“SELECT NEXT VALUE FOR StudentEnrollmentId”

I hope this might help you somewhere.

Enjoy !!!

Rj !!!

How to determine free space on each fixed drive of server machine using SQL SERVER ? TIP #75

 

When you do  SQL Server maintenance one of important aspect is available space on server drive because your SQL SERVER data is dependent on space Smile.

Now what you need to do to get free space from each drive ?

Just create a simple job which run on daily basis which send you space report on daily basis.

This available space report help you to prepare yourself for next step.

The job will content a simple SQL statement which is

EXEC MASTER..xp_fixeddrives

When you run this command in your SQL Management Studio you will get a tabular result which have 2 columns which is drive & Free space in MB.

As shown in below figure

fixed_drive_space_by_Sql_server_indiandotnet

I hope this might help you somewhere.

Thanks & Enjoy!!!

RJ!!

Merge Statement one of the way to synch destination from source table TIP# 58

 

Problem:-  Suppose you have a source table and one destination table and you want to synch destination table from source table. means if record is already exists then you need to update and the record with latest value and insert the record if record is not exists.

Solution:-  Although there are various ways exist but here we are using a new feature came in SQL Server 2008 which is MEGE statement.

Suppose we have a  tblStudentSource table and one tblStudentDestination table

both have equal columns as shown in below figure.

both have studentId, Firstname, LastName & course column

 

table_Structure_Merge

if you see above figure you will find that destination table there are 2 rows while in tblstudentSource table we have 3 rows.

Now if you see in tblStudentDestination LastName of StudentID  2 is spelled wrong.

So what we have to do here. We have to Insert a new row from tblStudentSource and update existing row.

To achieve this we will use merge statement as shown in figure

Basic_Merged_Statement

Now if you see above snap  we write basic merged statement which check condition of tblSource and destination table and if condition is matched then we wrote update statement if not matched then we wrote insert statement.

MERGE tblStudentDestination AS Destination
USING tblStudentSource  AS Source
ON Source.StudentId = Destination.StudentId
WHEN MATCHED THEN
UPDATE
SET Destination.FirstName = Source.FirstName,
    Destination.LastName = Source.LastName,
    Destination.Course = Source.Course
WHEN NOT MATCHED THEN
INSERT (FirstName,LastName,Course)
VALUES (Source.FirstName,source.LastName,Source.Course);

    

Now we run above statement we got the desire result and our source and destination table is in synched.

I hope this post may help and give you a general Idea of merge statement.

Thanks!!!

RJ!!!

Maintenance of fragmented Heap table–TIP #55

 

Friends,

In last post  post #54  we understood what is a Heap table. Now the challenge is what if this table is highly fragmented so.

Now our task is to run maintenance of this highly fragmented table. For performance it is require to maintain this type of heap table also.

So you have to run following command

“ALTER TABLE  TABLENAME REBUILD;

For example  I run the Rebuild for  Adventureworks2012 database’s heap table which we found in post #54.

See below snap for detail

Rebuild_Heap 

So, with this REBUILD command the table is rebuild and the fragmentation issue resolved.

I hope it may help you some where.

Thanks & Enjoy!!!

RJ!!

What is Heap table in SQL Server and How to get all the heap table from a database ? TIP # 54

 

Heap table:-

A table without cluster index is called Heap table. Now you are thinking why we are talking about this.

So as  SQL developer we should aware and In next post I will explain which is related to maintenance how to Rebuild a heap table.

Now our next question is how to determine all the heap table from a database

so below is simplest query to determine all the heap tables from a  database

SELECT T.Name ‘Heaptable’
FROM sys.indexes I    
    INNER JOIN sys.tables T
        ON I.object_id = T.object_id
WHERE I.type = 0 AND T.type = ‘U’

I ran this query on Adventureworks database and 2 tables with heap as shown in below figure

heaptable

now in next post we will discuss how to rebuild fragmented heap table which is a maintenance activity.

I hope this article may help you.

Thanks !!!

RJ!!

Row_Number() function for providing sequence number as per your wish TIP #35

 

Sometimes we require sequence column or we can a row number column so we have ROW_NUMBER() function in SQL SERVER.

Lets understand this by following example

In this example we have  person table which have firstname, last name, middlename  columns. Now we want to fetch records  with a extra column ROW_Number() so we write following query

SELECT ROW_NUMBER() OVER (ORDER BY LastName) As ROWNO,FirstName,MiddleName,LastName  FROM [Person].[Person]

When we run above query we get following result set. If you see below result set we have one extra column which is ROWNO

Row_Number

Lets understand the query in this we are getting sequence or Row number order by Last name.

There is one more option with ROW_NUMBER which is partition by

lets understand this by below query what we want for we want row number for each record based on last name means whenever new last name introduce row number start again from one. to achieve this we have to write following query

SELECT ROW_NUMBER() OVER (PARTITION BY LastName ORDER BY LastName) As rowNo,FirstName,MiddleName,LastName  FROM [Person].[Person]

See below snap when we run we get following result

row_number_Over

If you see above snap you will find that when the Last name change row number also change so we did partition by last name means for each change.

I hope you might have used earlier but I thought It might be possible it will helpful to someone who not aware.

Enjoy!!

RJ

VNext – A new version of Visual Studio (VS-2014)

 

Recently, Microsoft launched a new version of Visual Studio which is VS 2014 (VNext). Although I am not always happy with such a quick versions by Microsoft but this time it will a great NEWS and give a strong competition to all the open source competitors.

VNext is a open source. With VNext you will get MVC 6 , .NET framework 4.5.3.

You can download  the VNext from following link

http://www.visualstudio.com/en-us/downloads/visual-studio-14-ctp-vs

In VNext you will found a lot change. Major highlights

1) Project solution change

2) New configuration file

3) New dependency (reference) utility

4)Roslyn Compiler

5) web.Config removed

6)”K” factor

7) Next Generation “JIT”

8)Deployment changes

There are many more things which we will discuss later on.

Till Enjoy VNext.

Thanks & Best Regards,

Rajat Jaiswal

FIFA world cup 2014 with Angular.js & ASP.NET – Part- II

Dear Friends,
In last post part 1 ,we learnt how to use Angular.js.
Now in this post we are moving further as I shared we will do some more interesting thing here.
In this demo I have created same type of JSON as we have created in last post but it has some more values like
MatchDate: ‘Localtime’,
MatchNumber: ‘Match64’,
GroupName: ‘Final’,
Stadium: ‘Maracanã-EstádioJornalistaMárioFilho’,
Venue: ‘RioDeJaneiro’,
TeamName1: ‘W61’,
TeamCode1: ‘W61’,
TeamName2: ‘W62’,
TeamCode2: ‘W62’

JSONformat
Thanks to Fifa.com  for providing the information.

I created the JSON from FIFA.com’s data (I hope they will not mind it.)
Now as mention earlier, it is exactly same as we did in last post it has module, controller and groups properties.
In this we added two more features which is Order By & filter.
Firstly order by expression. So we have used order by expression in ng-repeat.

We have sorted this with matchDate with Order by expression as shown in below image

ng_Order_By
When you run it then your result will be in order by MatchDate

Match
Now what if you want to sort it by reverse date order then it is also very simple you just need to add “-“minus sign in front of MatchDate once you will do this you will get following output.

matchdate_with_ReveseOrder

Now we are interested in some specific information like particular group, particular match, and particular team so in such case we require Filter extension property.
In current example we have taken a textbox which has property ng-model. Now this ng-model value is passed in ng-repeat with filter option as shown in below image

filter_create
Now when you type value in textbox you will get filtered result on the screen as

Filter_Fifa_Result

I hope you enjoy it.

Soon I will share code with a other example.

 

Thanks & Enjoy !!!

Rajat