-
In this series at Part 1 Removing unwanted characters , I posted a method that used a function. In part 2 I posted a method that used Dynamic SQL. Here is another improved method that uses Dynamic SQL --Create test data create table #data (data varchar(100)) insert #data select 'tes^@&t %stri...
-
Run the following code declare @t table(i int unique) insert into @t select 1 union all select null union all select null You will get the following error Msg 2627, Level 14, State 1, Line 3 Violation of UNIQUE KEY constraint 'UQ__#A989971__3BD019979D26A3BB'. Cannot insert duplicate key in object...
-
Did you know that table variables are stored in the tempdb database for execution scope only?. Ok. Let us run this code declare @t table(i int) select * from tempdb.INFORMATION_SCHEMA.TABLES The result is TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ----------------------------------------------...
-
As you know I have started a new series on "Exploring SSMS" by which I will be posting some features that are available in SSMS which will be very helping during the development. In this post we will see how to open query window at default when opening SSMS. When you start SSMS, by default...
-
Often peple ask this question in the forum. "I created a procedure with proper alignment. But when I wanted to alter the procedure, result of sp_helptext did not keep the original alignment. How do I keep the original format?" Well. One answer is to make use of Script Stored Procedure -->...
-
In this post Dynamic PIVOT in SQL Server 2005 , I have shown a way to dynamically generate the column values with names. I used to get mails frequently from people on how to pass parameters when executing the procedure. The following will find total sales made by each employee for each year(from Employees...
-
Without running this query, can you guess the output? Query : select 999'''45' Output : Result 1 : Incorrect syntax near ' Result 2 : 999 Result 3 : 999'45 Result 4 : invalid column name 45
-
I have always been inspired by a particular quote from Arthur C Clarke - “ The only way of finding the limits of the possible is by going beyond them into the impossible ”. The more you think of it, the more you make sense out of it. If you decide in your mind that something is impossible, you will never...
-
As you know I have started a new series on "Exploring SSMS" by which I will be posting some features that are available in SSMS which will be very helping during the development. In this post we will see how to insert code template for creating a table, stored procedure, login etc in the SQL...
-
As you know I have started a new series on "Exploring SSMS" by which I will be posting some features that are available in SSMS which will be very helping during the development. In this post we will see how to know to hide system databases from the SQL Server management studio. In the Tool...
-
Square braces in SQL Server play a major role in T-SQL programming. When an object name contains a space, special character, etc, the only way to express them is to put them aroud squre braces. Consider that you want to create a table user master (with spaces between user and master), you can use [user...
-
As you all know tempdb is responsible of storing all the temporary objects created for a server. One of my co-workers asked me "what is the scope of permanent tables created in the tempdb database?". The answer is it can be accessed via tempdb from various connections just like objects are...
-
Without running these queries can you find out the query that would throw an error? 1 select 7*-2&.3 2 select 7|2&-3 3 select 7+-(.2)%(+3) 4 select ((7&2%3))
-
One of the members in the forum asked about using decimal datatype in identity column. The question was "Is decimal point allowed in the identity column?" Ok. The identity column can never have any decimal points. It is a whole number. The decimal datatype can be used to have a bigger number...
-
When a datatype timestamp or rowversion is used, it generates unique value whenever a row is added or updated in a table. It is one of the ways to generate auto-generated values not only for insertion but for updation too. If you want to know the minimum active rowvertion number for a database, you can...