Problem while using MAX function

create table emp
(e_id nvarchar(50) primary key,
ename varchar(50))

e_id | ename
a1 x
a2 y
a.. n..
a15 z

select max(e_id) from emp

output :
a9

you can notice that above out put is wrong
the answer as to be a15….

Problem:
You will get an insight by running this query:
SELECT e_id from Emp ORDER BY e_id

You will see that e_id is being sorted by the character’s sequence and not like numbers. So, a10 will be followed by a1.
So, as per sort logic, a9 is the max value.

Answer:

SELECT LEFT(e_id, 1) + CAST(MAX(CAST(SUBSTRING(e_id, 2, LEN(e_id)) AS INT)) AS NVARCHAR(50)) FROM Emp GROUP BY LEFT(e_id, 1)

Please note that this query is based on the first character of the e_id column values. If let say someone inserts another 15 records starting from b1 , x to b15, z then the output of this query will be of 2 rows:
a15
b15

Warm Regards,

Siddhesh

SQL SERVER – What is Denali?

“What is Denali?”

Denali is code name of SQL Server 2011.

Here is the list of the code name of other versions of SQL Server.

In 1988, Microsoft released its first version of SQL Server. It was developed jointly by Microsoft and Sybase for the OS/2 platform.

• 1993 – SQL Server 4.21 for Windows NT
• 1995 – SQL Server 6.0, codenamed SQL95
• 1996 – SQL Server 6.5, codenamed Hydra
• 1999 – SQL Server 7.0, codenamed Sphinx
• 1999 – SQL Server 7.0 OLAP, codenamed Plato
• 2000 – SQL Server 2000 32-bit, codenamed Shiloh (version 8.0)
• 2003 – SQL Server 2000 64-bit, codenamed Liberty
• 2005 – SQL Server 2005, codenamed Yukon (version 9.0)
• 2008 – SQL Server 2008, codenamed Katmai (version 10.0)
• 2010 – SQL Server 2008 R2, Codenamed Kilimanjaro (aka KJ)
• Next – SQL Server 2011, Codenamed Denali

Any guess' what should be the next version after 2011 should be codenamed?

SQL Server - Select All Rows rather than Select TOP 1000

When you right Click on a table, you simply find the first option Select TOP 1000 rows.

But you can change it to ALL rows i.e. all the rows will be displayed without limitations.

Just follow these steps.

GoTo:

Make sure that SQL Management studio is open.

Then goto Tools --> Options --> SQL Server Object Explorer

Same as in image.

Then select Commands in it

& then set all the values 0

Click on OK

That's it !!

SQL SERVER – Difference Between GETDATE and SYSDATETIME

Sometime something so simple skips our mind. I never knew the difference between GETDATE and SYSDATETIME. I just ran simple query as following and realized the difference.

SELECT GETDATE() fn_GetDate, SYSDATETIME() fn_SysDateTime

In case of GETDATE the precision is till miliseconds and in case of SYSDATETIME the precision is till nanoseconds.

Now the questions is to you – did you know this? Be honest and please share your views. I already accepted that I did not know this in very first line.

This applies to SQL Server 2008 only.

SQL SERVER – The Self Join – Inner Join and Outer Join

Self Join has always been an note-worthy case. It is interesting to ask questions on self join in a room full of developers. I often ask – if there are three kind of joins, i.e.- Inner Join, Outer Join and Cross Join; what type of join is Self Join? The usual answer is that it is an Inner Join. In fact, it can be classified under any type of join. I have previously written about this in my interview questions and answers series. I have also mentioned this subject when I explained the joins in detail over SQL SERVER – Introduction to JOINs – Basic of JOINs.

When I mention that Self Join can be the outer join, I often get a request for an example for the same. I have created example using AdventureWorks Database of Self Join earlier, but that was meant for inner join as well. Let us create a new example today, where we will see how Self Join can be implemented as an Inner Join as well as Outer Join.

Let us first create the same table for an employee. One of the columns in the same table contains the ID of manger, who is also an employee for the same company. This way, all the employees and their managers are present in the same table. If we want to find the manager of a particular employee, we need use self join.

USE TempDb
GO
-- Create a Table
CREATE TABLE Employee(
EmployeeID INT PRIMARY KEY,
Name NVARCHAR(50),
ManagerID INT
)
GO
-- Insert Sample Data
INSERT INTO Employee
SELECT 1, 'Mike', 3
UNION ALL
SELECT 2, 'David', 3
UNION ALL
SELECT 3, 'Roger', NULL
UNION ALL
SELECT 4, 'Marry',2
UNION ALL
SELECT 5, 'Joseph',2
UNION ALL
SELECT 7, 'Ben',2
GO
-- Check the data
SELECT *
FROM Employee
GO

We will now use inner join to find the employees and their managers’ details.

-- Inner Join
SELECT e1.Name EmployeeName, e2.name AS ManagerName
FROM Employee e1
INNER JOIN Employee e2
ON e1.ManagerID = e2.EmployeeID
GO

From the result set, we can see that all the employees who have a manager are visible. However we are unable to find out the top manager of the company as he is not visible in our resultset. The reason for the same is that due to inner join, his name is filtered out. Inner join does not bring any result which does not have manager id. Let us convert Inner Join to Outer Join and then see the resultset.

-- Outer Join
SELECT e1.Name EmployeeName, ISNULL(e2.name, 'Top Manager') AS ManagerName
FROM Employee e1
LEFT JOIN Employee e2
ON e1.ManagerID = e2.EmployeeID
GO

Once we convert Inner Join to Outer Join, we can see the Top Manager as well. Here we have seen how Self Join can behave as an inner join as well as an outer join.

As I said earlier, many of you know these details, but there are many who are still confused about this concept. I hope that this concept is clear from this post.

SQL SERVER – ERROR – FIX – Msg 3702, Level 16, State 3, Line 1 Cannot drop database “MyDBName” because it is currently in use

I tried to drop a table, but while trying the same. I got the following Error :

Msg 3702, Level 16, State 3, Line 1
Cannot drop database “MyDBName” because it is currently in use.

The reason was very simple as my database was in use by another session or window. I had option that I should go and find open session and close it right away; later followed by dropping the database. As I was in rush I quickly wrote down following code and I was able to successfully drop the database.

USE MASTER
GO
ALTER DATABASE MyDBName
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE MyDBName
GO

 
Please note that I am doing all this on  my demonstrations, do not run
above code on production without proper  approvals and supervisions.


Thanks !! :)
-Siddhesh

ROW_NUMBER (Transact-SQL)

Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
Transact-SQL Syntax Conventions

Syntax:

ROW_NUMBER ()   OVER ( [ <partition_by_clause> ] <order_by_clause> ) 

Examples

---

A. Returning the row number for salespeople

The following example returns the ROW_NUMBER for the salespeople in AdventureWorks2008R2 based on the year-to-date sales.

SELECT FirstName, LastName, ROW_NUMBER() OVER(ORDER BY SalesYTD DESC)AS 
'Row Number', SalesYTD, PostalCode 

FROM Sales.vSalesPerson

WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0

B. Returning a subset of rows

The following example returns rows with numbers 50 to 60 inclusive in the order of the OrderDate.

USE AdventureWorks2008R2;
GO
WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
    FROM Sales.SalesOrderHeader 
) 
SELECT * 
FROM OrderedOrders 
WHERE RowNumber BETWEEN 50 AND 60

C. Using ROW_NUMBER() with PARTITION

The following example shows using the ROW_NUMBER function with the PARTITION BY argument.

SELECT FirstName, LastName, ROW_NUMBER() OVER(PARTITION BY PostalCode
ORDER BY SalesYTD DESC) AS 'Row Number', SalesYTD, PostalCode 
FROM Sales.vSalesPerson
WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0;

Program To Print Numbers in Cylindrical(Maze) Format

Hi,

Program Definition :: Print Numbers in Cyclical (Maze) Format

Explaination:

Here numbers are to be printed in cyclical format i.e. suppose i enter 4 as input then the output should be of 4×4 in this format

1           2              3            4

12        13            14            5

11        16            15            6

10         9              8             7

If anyone has the answer then please answer it.

Regards,

Siddhesh

HTML Color Codes

Major hexadecimal color codes

Color	Color Code	Color	Color Code
Red	#FF0000	White	#FFFFFF
Turquoise	#00FFFF	Light Grey	#C0C0C0
Light Blue	#0000FF	Dark Grey	#808080
Dark Blue	#0000A0	Black	#000000
Light Purple	#FF0080	Orange	#FF8040
Dark Purple	#800080	Brown	#804000
Yellow	#FFFF00	Burgundy	#800000
Pastel Green	#00FF00	Forest Green	#808000
Pink	#FF00FF	Grass Green	#408080

CASE/WHEN return type

This post is related to how the Case/When statement return value after the executing condition available.

Problem

Recently I am working on the query where I have to sort data based on the user input but each column in table has different datatype. So to meet with this type of requirement I use case when.. block of the sql server. something as below..

DECLARE @sortby Varchar(10)
Set @sortby = 'A1'

Select String1,String2,DateCol
from (
select 'A1','B1', GetDateTime() 
union
select 'A2','B2', GetDateTime()
)   As d(String1,String2,DateCol)
Order by
Case
When @sortby = 'A1' then String2
When @sortby = 'B1' then String1
When @sortby = 'Date' then Date1 
End

there is not syntax error when you check for the syntax error but when execute code you find there is error

ERROR:Conversion failed when converting datetime from character string.

the problem here is first two having datatype Varchar and last one having datatype DateTime. So when you executing its found that one branch having datetime so its gives error even its not executing that.

Solution

Solution 1.

So to avoid this problem you require to convert dateTime to the string

DECLARE @sortby Varchar(10)
Set @sortby = 'A1'

Select String1,String2,DateCol
from (
   select 'A1','B1', GetDateTime() 
   union
   select 'A2','B2', GetDateTime()
)   As d(String1,String2,DateCol)
Order by
Case
      When @sortby = 'A1' then String2
      When @sortby = 'B1' then String1
      When @sortby = 'Date' then Cast(Date1   as varchar(20))
End

Solution 2.

Divide the Case when in multiple statement

DECLARE @sortby Varchar(10)
Set @sortby = 'A1'

Select String1,String2,DateCol
from (
select 'A1','B1', GetDateTime() 
union
select 'A2','B2', GetDateTime()
)   As d(String1,String2,DateCol)
Order by
Case When @sortby = 'A1' then String2 End,
Case When @sortby = 'B1' then String1 End,
Case When @sortby = 'Date' then Date1   End 

Happy Coding !!
have a nice day........
:)

Get search key word from the referrer url.

 C#.Net

This is a very simple way to do actually. Web developers are very much familiar to work with the referrer url. The HTTP_REFERER server variable return the referrer url. In this tips I will show how easily we can get the search key word from the referrer url.

public string GetSearchKeyWords(string strQuery)
{
string result = "";
string pattern = "\b\w*p=(?!u)\w*\b|\b\w*q=(?!u)\w*\b|\b\w*qs=(?!u)\w*\b" + "|\b\w*encquery=(?!u)\w*\b|\b\w*k=(?!u)\w*\b\b\w*qt=(?!u)\w*\b" + "|\b\w*query=(?!u)\w*\b|\b\w*rdata=(?!u)\w*\b|\b\w*search_word=(?!u)\w*\b" + "|\b\w*szukaj|terms=(?!u)\w*\b\b\w*text=(?!u)\w*\b|\b\w*wd=(?!u)\w*\b|\b\w*words=(?!u)\w*\b";

foreach (Match m in Regex.Matches(strQuery, pattern))
{
// get the matched string
string x = m.ToString();
x = x.Substring(1, x.Length - 1);
// collect all text result += x;
}
return result.Replace("=", "");
}



Input: http://www.google.com/url?sa=t&source=web&cd=7&ved=0CEQQFjAG&url=http%3A%2F%2Fwww.example.com%2F&rct=j&q=developersproject.com=uSLVTMm0JYXNhAewqbzjBA&usg=AFQjCNFtfSFIuj4fxnl8bD_bR2NgzDePGw&cad=rja


Output: developersproject.com

SQL SERVER – Find Row Count in Table – Find Largest Table in Database

Here's the Script :

SELECT sc.name +'.'+ ta.name TableName
,SUM(pa.rows) RowCnt
FROM sys.tables ta
INNER JOIN sys.partitions pa
ON pa.OBJECT_ID = ta.OBJECT_ID
INNER JOIN sys.schemas sc
ON ta.schema_id = sc.schema_id
WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
GROUP BY sc.name,ta.name
ORDER BY SUM(pa.rows) DESC


Above query will return following resultset.

You can see in resultset that tablename is prefixed with Schema name and numbers of the rows are listed in the table.

If you are using earlier version of SQL Server 2000 you may want to run following command to update the pages and its statistics.

DBCC updateusage(adventureworks)

If you have better query to find out table row count, please share here.

SQL SERVER – SELF JOIN Not Allowed in Indexed View – Limitation of the View

Let us first create the same table for an employee. One of the columns in this table contains the ID of the manger, who is an employee of that company, at the same time. This way, all the employees and their managers are present in the same table. If we want to find the manager of a particular employee, we need to use Self Join.

USE TempDb
GO
-- Create a Table
CREATE TABLE Employee(
EmployeeID INT PRIMARY KEY,
Name NVARCHAR(50),
ManagerID INT
)
GO
-- Insert Sample Data
INSERT INTO Employee
SELECT 1, 'Mike', 3
UNION ALL
SELECT 2, 'David', 3
UNION ALL
SELECT 3, 'Roger', NULL
UNION ALL
SELECT 4, 'Marry',2
UNION ALL
SELECT 5, 'Joseph',2
UNION ALL
SELECT 7, 'Ben',2
GO
-- Check the data
SELECT *
FROM Employee
GO

We will now utilize Inner Join to find the employees and their managers’ details.

-- Inner Join
SELECT e1.Name EmployeeName, e2.name AS ManagerName
FROM Employee e1
INNER JOIN Employee e2
ON e1.ManagerID = e2.EmployeeID
GO

Now let us try to create View on the table. This will allow well construction of the View without any issues associated with it.

-- Create a View
CREATE VIEW myJoinView
WITH SCHEMABINDING
AS
SELECT e1.Name EmployeeName, e2.name AS ManagerName
FROM dbo.Employee e1
INNER JOIN dbo.Employee e2
ON e1.ManagerID = e2.EmployeeID
GO


Now let us try to create a Clustered Index on the View.

-- Attempt to Create Index on View will thrown an error
CREATE UNIQUE CLUSTERED INDEX [IX_MyJoinView] ON [dbo].[myJoinView]
(
[EmployeeName] ASC
)
GO

Unfortunately, the above attempt will not allow you to create the Clustered Index, as evidenced by an error message. It will throw following error suggesting that SELF JOIN is now allowed in the table.

Msg 1947, Level 16, State 1, Line 2
Cannot create index on view “tempdb.dbo.myJoinView”. The view contains a self join on “tempdb.dbo.Employee”.

The generic reason provided is that it is very expensive to manage the view for SQL Server when SELF JOIN is implemented in the query.
If any of you has a better explanation of this subject, please post it here through your comments, and I will publish it with due credit.

SQL SERVER – COUNT(*) Not Allowed but COUNT_BIG(*) Allowed – Limitation of the View 5

One of the most prominent limitations of the View it is that it does not support COUNT(*); however, it can support COUNT_BIG(*) operator. In the following case, you see that if View has COUNT (*) in it already, it cannot have a clustered index on it. On the other hand, a similar index would be created if we change the COUNT (*) to COUNT_BIG (*).For an easier understanding of this topic, let us see the example here.

USE tempdb
GO
IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[SampleView]'))
DROP VIEW [dbo].[SampleView]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[mySampleTable]') AND TYPE IN (N'U'))
DROP TABLE [dbo].[mySampleTable]
GO
-- Create SampleTable
CREATE TABLE mySampleTable (ID1 INT, ID2 INT, SomeData VARCHAR(100))
INSERT INTO mySampleTable (ID1,ID2,SomeData)
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY o1.name),
ROW_NUMBER() OVER (ORDER BY o2.name),
o2.name
FROM sys.all_objects o1
CROSS JOIN sys.all_objects o2
GO
-- Create View
CREATE VIEW SampleView
WITH SCHEMABINDING
AS
SELECT COUNT(*) TableCount, ID2
FROM dbo.mySampleTable
GROUP BY ID2
GO
-- Create Index on View
CREATE UNIQUE CLUSTERED INDEX [IX_ViewSample] ON [dbo].[SampleView]
(
ID2 ASC
)
GO
/* Above statement will thrown an error
Msg 10136, Level 16, State 1, Line 1
Cannot create index on view "tempdb.dbo.SampleView" because it uses the aggregate COUNT. Use COUNT_BIG instead.
*/
-- Aleter View to replace COUNT with BIG_COUNT
ALTER VIEW SampleView
WITH SCHEMABINDING
AS
SELECT COUNT_BIG(*) TableCount, ID2
FROM dbo.mySampleTable
GROUP BY ID2
GO
-- Now let us create Index again - this time successfully
CREATE UNIQUE CLUSTERED INDEX [IX_ViewSample] ON [dbo].[SampleView]
(
ID2 ASC
)
GO

Here is a screenshot of an error that occurred when Views used COUNT(*) and there was an attempt to create an Index on it.

If you are wondering about the reason behind allowing COUNT_BIG and COUNT, here is a quick explanation for this. Itzik Ben-Gan explained me the reason for this situation. He has also clarified the reasons in his book series, ‘Inside T-SQL‘. Here is a concise summary of the explanation. This response from Itzik is produced unaltered and with his permissions:

If the query is a grouped query, SQL Server needs to keep track of the count in each group in order to known whether a group needs to be modified or removed altogether upon DELETE/UPDATE of rows against the underlying tables. As for why the COUNT_BIG and not just COUNT, since SQL Server materializes the counts along with the rest of the view’s data, I guess this has to do with support for groups with more rows than the maximum four-byte integer.

BTW, unrelated to views but along similar lines, see what happens if you add to a clustered table more than the maximum four-byte integer number of rows with the same non-unique clustered index key. The uniqueifiers SQL Server uses internally to distinguish between rows with the same clustered index key is a four-byte integer. Once it overflows, you get error 666 and are not allowed to add more rows with the same clustered index key.

Now, with uniqueifiers for clustering keys I understand the choice to go for four bytes since there are great space savings and therefore read performance benefits as a result, and we are talking about an extreme case for this to happen . But with grouped queries, usually the number of groups is not too large, but groups themselves can be large. Imagine a situation where you try to add more rows to a table that has an indexed view and SQL Server rejects the insert because of a four-byte int overflow in the target group count.

I hope it is clear now. If you want to learn more about this, you can continue reading his book Inside T-SQL.

Let me know what you think of these limitations, as well as your opinions about the example.

SQL SERVER – UNION Not Allowed but OR Allowed in Index View – Limitation of the View 6

Update: Please read the summary post of all the 11 Limitations of the view SQL SERVER – The Limitations of the Views – Eleven and more…

If you want to create an Indexed View, you ought to know that UNION Operation is now allowed in Indexed View. It is quite surprising at times when the UNION operation looks very innocent and seems that it cannot be used in the View.

Before an in-depth understanding this subject, let me show you a script where UNION is now allowed in Indexed View:

USE tempdb
GO
IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[SampleView]'))
DROP VIEW [dbo].[SampleView]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[mySampleTable]') AND TYPE IN (N'U'))
DROP TABLE [dbo].[mySampleTable]
GO
-- Create SampleTable
CREATE TABLE mySampleTable (ID1 INT, ID2 INT, SomeData VARCHAR(100))
INSERT INTO mySampleTable (ID1,ID2,SomeData)
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY o1.name),
ROW_NUMBER() OVER (ORDER BY o2.name),
o2.name
FROM sys.all_objects o1
CROSS JOIN sys.all_objects o2
GO
-- Create View
CREATE VIEW SampleView
WITH SCHEMABINDING
AS
SELECT ID1,ID2,SomeData
FROM dbo.mySampleTable
WHERE ID1 < 1000
UNION
SELECT ID1,ID2,SomeData
FROM dbo.mySampleTable
WHERE ID2 < 1000
GO
-- Create Index on View
CREATE UNIQUE CLUSTERED INDEX [IX_ViewSample] ON [dbo].[SampleView]
(
ID2 ASC
)
GO
/* Above statement will thrown an error
Msg 10116, Level 16, State 1, Line 1
Cannot create index on view 'tempdb.dbo.SampleView' because it contains one or more UNION, INTERSECT, or EXCEPT operators. Consider creating a separate indexed view for each query that is an input to the UNION, INTERSECT, or EXCEPT operators of the original view.
*/
-- Aleter View to replace COUNT with BIG_COUNT
ALTER VIEW SampleView
WITH SCHEMABINDING
AS
SELECT ID1,ID2,SomeData
FROM dbo.mySampleTable
WHERE ID1 < 1000 OR ID2 < 1000
GO
-- Now let us create Index again - this time successfully
CREATE UNIQUE CLUSTERED INDEX [IX_ViewSample] ON [dbo].[SampleView]
(
ID2 ASC
)
GO


During the script, the following ERROR would occur if you try to create the index while UNION operation is in the view:

Msg 10116, Level 16, State 1, Line 1
Cannot create index on view ‘tempdb.dbo.SampleView’ because it contains one or more UNION, INTERSECT, or EXCEPT operators. Consider creating a separate indexed view for each query that is an input to the UNION, INTERSECT, or EXCEPT operators of the original view.

In contrast to this converting the UNION to OR operation would give the same result, plus it would allow you to create an index on the View. Well, our example is one in which we are able to re-write the script with OR clause. However, keep in mind that there can be cases where it is not possible to re-write and you might end up not using Views with Index.

In this series, I have been writing about many limitations and their explanation. Now here are my questions for you: