Row_Number SQL Server

Row_Number or Sequence Generator in SQL Server Is is used to generate a sequence number or a row number in a result table. That is if you want to run a query and you rank certain records in certain order, this where you can use Row Number function. It is a function and is follow be () Usage select ID, row_number() over(order by id) as RowNumber from mytable will create the sequence number in ORDER based on id. This will be the simplest squence. When ever ID changes, row number is incremented. Since ID is our primary key, it changes…

Read More

compare two null values in sql

If you compare two values and either of them can be null or one of them null equal (=) operator does not work. Equal operator is not null sensitive. To compare the two you have to changed null to valid string value and then apply = operator. declare @v1 varchar(20) declare @v2 varchar(20) if (isnull(@v1,’0′) = isnull(@v2,’0′)) select ‘Match Found’ else select ‘Match not found’ The above code demonstrates the correct way of comparing null values. if (@v1 = @v2) select ‘Match Found’ else select ‘Match not found’ the above code will always return false if either end is null…

Read More

SQL Server 2005 Instances do not show up in Server 2008 R2

I updated my SQL Server 2005 to SQL Server 2008. Everything worked great. After a few weaks later, I tried to connect to the db in SQL Server Management Studio after I had restarted the PC and it won’t connect. Fired up SQL Server Configuration Manager and it wont show any single instance of the SQL Servers. The problem? After I upgraded to SQL Server 2008, I should have started SQL Server Configuration Manager 2008 not 2005. It appears SSCM 2005 does not show 2008 instances after they were converted to 2008.

Read More

JOIN using LIKE in SQL

Q. Can I join tow tables using like (more flexible criteria) than exact match? Ans. Yes

Table A and B

Result

Read More

BETWEEN DATES SQL Server 2008

When dealing with dates, remember there is not such field as date in SQL Server 2005/2008. There is datetime field. For reference there is a date type in MySQL which I think is very useful. There could be problems when you use between dates in SQL Server 2008. The problem comes because datetime has time information also and that give a new meaning when you are doing comparision. Lets get the current date.

If I do

This is because when comparing dates, sql server 2008/2005 compares time information also. if the time information is 00:00:00, in that case…

Read More

Error Locating Server Instance Specified

Connecting to the server through a third party software/interface can give you the following error. [SQL Native Client]SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. [SQL Native Client]Login timeout expired [SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. This basically means, the SQL Server location setting is wrong. This can be resolved in two ways Either specify the Server instance (for Named Server Example: SERVER = MYPCNAME\SQLEXPRESS Example: SERVER…

Read More

SQL Select * into from

If you want to copy or backup your table in the database, use the following SQL command. select * into newtable from oldtable The new table is created on the fly. You do not need to create it. A more descriptive example is below. Not that # means temporary table. create table #table1( id int not null primary key identity, name varchar(15), fav_game varchar(20) ) insert into #table1 values(‘john’,’hockey’) insert into #table1 values(‘Kim’,’footbal’) insert into #table1 values(‘Joe’,’soccer’) select * from #table1 — at this point only #table1 exists — lets say want to want to backup table1, we will use…

Read More