-
Today my friend asked me that ISNUMERIC function is not working properly. I checked, the issue was with some special char, might be MSSQL will improve this functionality in coming versions. For now, if we change the way of calling, it will show you the correct result. These conditions shows wrong result...
-
Today one of my friend ask me, can we use Order by clause dynamically ? I said yes, but there are few limitations while using case in order by clause see the below example. This script will return Error: DECLARE @TOP INT = 10, @FLD VARCHAR(20)='AGENT_ID' SELECT TOP(@TOP) * FROM ( SELECT 1 AS...
-
Cross tab result is always required and it is bit complex in case of dynamic column. If column name has to be dynamic then this is the good idea to have the PIVOT Query, for example I have 3 Tables and data like this: First Table ----------------------------------------------------------- IF OBJECT_ID...
-
We can use this script to show the row data in string. Name ------ RAM SHYAM HARI RITA Name RAM,SHYAM,HARI,RITA --Use the following query DECLARE @strList varchar(100) SELECT @strList = COALESCE(@strList + ', ', '') + CAST(Name AS varchar(5)) FROM TABLE1 SELECT @strList
-
It is good if you backed up the LOG file also when we take the full backup of the database. If you are not backed up the .LDF file or LOG then it will become huge and occupy the large number of space, if you need to clean up the transaction log. Follow the following steps and clean your LOG size. 1)...
-
FIRST_VALUE() and LAST_VALUE() are new analytic function introduced in SQL Server "Denali". As the name suggests FIRST_VALUE() returns first value in an ordered set of values, and LAST_VALUE() returns the last value from an ordered set of values. For example, SELECT EmployeeID, FirstName, MiddleName...
Posted to
Vishal Gajjar's Blog
by
Vishal Gajjar
on
08-12-2011
Filed under:
Filed under: SQL FAQ, SQLServer, SQL, SQL "Denali", TSQL, Database, Sql And Me, Analytic Functions, BRH, Tricks, Tips &
-
Finding duplicate rows in a table can be done easily by using ROW_NUMBER() function. You can number each similar rows by using PARTITION BY clause. For example, consider the below table: EmployeeID FirstName MiddleName LastName ———– ———- ———–...
Posted to
Vishal Gajjar's Blog
by
Vishal Gajjar
on
08-11-2011
Filed under:
Filed under: SQL FAQ, SQLServer, SQL, TSQL, Database, Sql And Me, Common Table Expressions, System Functions, BRH, Tricks, Tips &
-
While using Scrolling in Visual Studio IDE, by default it scroll amount is fixed by number of lines. During normal development, when we scroll with in code editor, the scroll amount by fixed line number is fine. But if you zoom out the editor code, scrolling wont be useful as though we have...
-
While working with SQL Server, its common to have a saved script which you need to execute for different server or databases and usually you open this saved script, make changes according to requirements and execute. SQL Server 2008 has one more time-saving facility. You can save your script as template...
-
Here are a couple more SSMS tricks I’ve stumbled upon recently. Both involve the functionality of registered servers. If you really only deal with 1 or 2 servers in your environment, these probably won’t help you out much. But for those of us who have to deal with a large number of...
-
This is one that I figured most everyone would know, but repeatedly over the past couple weeks in my office, I was surprised to find that many people had never seen this before. If you just click on objects in the object explorer, you can’t hold shift to select multiple objects. This leads...
-
When comes to query writing, I think, I am so lazy ;) and always prefer less code. Recently I found OVER clause so helpful to write my code in more effect and more magical way. Most of us know the usage of OVER clause, when creating row number column for a given partition or even with out any partition...
-
This isn’t exactly the best thing since bread (or even sliced bread for that matter), but I figured I’d share this as I haven’t seen it written about in many places and I find it quite handy on a regular basis. If you hold down the ALT key in SSMS when you’re selecting text you...
-
08 September 2010 16:00 India This session is not about the technical aspects of being an Exceptional DBA. There are already many good resources available on this topic. This session is a guide that will show you, step-by-step, specifically what you can do to differentiate yourself from the crowd, so...