Alter an existing object rather than Drop and Create

In this blog I would like to write on a topic that I see many developers (and few DBA’s too) doing without trying to understand or think about the complete picture of what is affected. The task of making changes to an existing database object such as tables, stored procedures,  views etc.

Many times, database objects will have to be modified (altered) at a later time, for a table or view it would be adding new columns or changing the data types of the existing columns. For stored procedures it would be change in logic that needs to be incorporated. There are multiple ways to achieve this, one way is to drop the existing one and create the newer one (most probably for a view or stored procedure), the other way is to alter the existing one. What a user needs to keep in mind is, that when you drop a database object and create the new one, the underlying security permissions get whacked and those users / logins that have been assigned additional permissions to these objects will not have the permissions to carry out their task, they should be reassigned with those permissions on the newly created database.

Hence I feel, it is better to alter the existing one instead of dropping and creating.

Do you like this site? Like our FB page @\LearnSQLWithBru so that, you know when there is a new blog post.

– Bru Medishetty

Space function in SQL Server

Few weeks back, I wrote a blog on CONCAT string function in SQL Server 2012. A user asked if the CONCAT function is similar to Space function in SQL Server and what is the difference between the two functions. I replied to him and clarified the difference of the 2 functions. After that I thought, it would be good to write a blog post on Space function too.

Space function

Space function is useful to return ‘x’ number of spaces, where ‘x’ is an integer.  The syntax for the space function is space(x) where x is an integer. To understand the function lets us look a simple script with and without space function.

Without Space function

The below screen shot displays an output where 2 string values are concatenated without using space function and the resulting string is an output that may not be acceptable. See that the resulting string is a single string without any break between first and last name.

Using Space function

The below screen shot displays an output where 2 string values are concatenated and separated by space function. Using space(1) generated a single space (1 space) and when that is used in the string concatenation, output string is now better and acceptable. 

Do you like this site? Like our FB page @\LearnSQLWithBru to know when there is new content.

— Bru Medishetty

Using SELECT INTO to Create a Test Table

While working with SQL Server tables, especially when you are testing a certain code, you would want to create a test table that is similar to the actual / original table. That way you don’t have to worry about accidental updates or data loss etc.

During such situations, creating an an exact replica of that table can be achIeved using SELECT INTO statement. Below is a script to create an exact replica of Person.Address table found in AdventureWorks Sample Database.

SELECT * INTO AdventureWorks.dbo.Person_Address_Test
FROM AdventureWorks.Person.Address

Below is a screen shot of the query run on AdventureWorks DB on a SQL Server 2008 R2 Instance.

What this SELECT INTO does is, it creates a new table (if it does not exist) and inserts the data into that table. Since we have not filtered any rows with a WHERE clause, all the data from the table Person.Address is now readily available in the Test table. If there is a table that already exists with that chosen name, the statement fails, indicating there is already a table with that name.

Now you run any command on the newly created table, the below is a simple select statement with top 10 *.

Do you like this site? Like our FB page @\LearnSQLWithBru that let’s you know when there is a new blog post.

— Bru Medishetty