-
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...
-
It is very important to keep revisiting one’s code – especially, when the underlying platform changes or when teams start using newer features within legacy code. Until SQL Server 2005, the typical understanding of the keyword “DECLARE” was that it had only one meaning – it was used to define an object...
-
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
-
In my previous post in this tutorial ( here ), I introduced the system stored procedures that would allow us to create a SQL Trace, define events and data columns to capture and start the trace using nothing but pure T-SQL code. Today, we will see the following: How to use the graphical Profiler to generate...
Posted to
Nakul's Blog
by
Nakul Vachhrajani
on
03-07-2012
Filed under:
Filed under: Administration, T-SQL, Tips, SQL Server, #SQLServer, #TSQL, Best Practices, Guidance, DBA, SSMS, Tools and Utilities, Series
-
For those who have been following my posts, you would recollect that I had run a 6-part series on SQL Profiler tutorials some time ago. Based on the feedback received, I am now presenting a 4-part extension to the series. In this part, I will describe how the capturing of a SQL Trace can be done in an...
Posted to
Nakul's Blog
by
Nakul Vachhrajani
on
03-04-2012
Filed under:
Filed under: Administration, T-SQL, Performance Tuning, Tips, SQL Server, #SQLServer, #TSQL, Best Practices, DBA, SSMS, Tools and Utilities, Series
-
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...
-
In November 2011, I wrote a post titled SQL Server – Performance – Best Practice – Create Index Before or After a data insert? . The post dealt with answering the question - which method was better – whether to create an index before or after a bulk-insert? The post was essentially targeted to code that...
-
I recently had the opportunity to view some of the newest courses from Pluralsight , an organization that offers online training courses for developers. One of the courses that I viewed was the SQL Server Questions & Answers ( http://www.pluralsight-training.net/microsoft/Courses/TableOfContents...
Posted to
Nakul's Blog
by
Nakul Vachhrajani
on
02-22-2012
Filed under:
Filed under: Development, Administration, T-SQL, Performance Tuning, Tips, SQL Server, #SQLServer, #TSQL, Best Practices, Guidance, DBA, SSMS, Tools and Utilities, Community, Myths
-
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...