SQL: Rename Column/Table Name

Hi All,

We all sometimes need to rename a column/table from our database, however we do not want to drop all our records from the table where the column is renamed or the name of that table is changed.

For that there is such a nice SPROC in MS SQL: sp_rename.

This SPROC can be used to rename any user-created object in current DB.

However there is also a caution:

SQL Performance Tips 5

Hello,

Another SQL Performance tip, which will help you to determine the exact execution time of your sql statements. The simplest way of doing that is to save the time before running your statement and then compare that value with current time. Here is a sample SQL statement:

SQL Performance Tips 4

Hi All,

Again me with some more SQL Performance Tips.

If you have a lot of tables with which you have to join and those tables are big then pay attention to the order of how you join those tables because the order matters.

Why the order of the tables in joins matters?

It is because even if the result of your query returns only a few of records, lets say 7 records, it doesn't mean that it will run fast, and it will not run fast because during the first joins your tables can "return" thousands, millions or even more records with which the SQL Server will be working further.

So the idea is that you have to use the Actual Execution Plan to see what is Estimated Number of Rows with which the SQL Server will be working with.

Try to filter the data using the joins, so that less data remains to be processed, and less time it takes to the SQL Server to return the result to you.

Good luck,
Roman

SQL Performance Tips 1

Hi All,

By working at some performance issues with SQL Stored Procedures, Views and Functions I had to make some investigations on SQL performace, how to do that, I've been searching a lot to find some tips.

So the first tip is: Avoid using CURSORS.

Yes, avoid cursors, do everything to avoid cursors.

Often time people say to me that for their case it is not possible to avoid cursors, and often times I prove them that it is possible.

When there is just a few things that you could do with a set of data then try to do that in some statements by joining the data, instead of going through each item.

It is not always clear that cursors could be avoided, this is why you have to think several times if it is really not possible to avoid them.

Regards,
Roman

MS SQL RowID equivalent

Hello everyone,

Many MS SQL developers ask if there is an equivalent to RowID from ORACLE in MS SQL.

The answers is NO.

But there is a workaround. You can use ROW_NUMBER() instead and where you will need to specify based on which columns to generate RowID, the order in which the ROW_NUMBER value is assigned to the rows in a partition.

Here is an example:

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

You can see more details on Microsoft's page about ROW_NUMBER() function: http://msdn.microsoft.com/en-us/library/ms186734.aspx

Simple, isn't it?