Hi,

Many a times in a query we need to limit down the number of records returned in the query. This can be done in two ways using the TOP keyword or the row count keyword.

Select top 10 from Table

This will return the top 10 records from the query. We can also make this dynamic by making the Value in Top keyword to come from a variable.

DECLARE @top INT
SET @top = 10
SELECT TOP(@top) * FROM Table

Hi,

Many a time with all the data we want to store the current date time for reference purpose.  If your application is going to work with more than one country than its best to store the date time in UTC (GMT) format.  With difference database the way to get the current UTC date time is different. Here is a list of way to do the same for different database.

Oracle 9i and above

select new_time(sysdate,'EST','GMT') into Result  from dual;

Don’t forget to change EST with the current time zone of the database.

MS SQL 200 and above

GETUTCDATE()

Hi,

As all of us know that SQL server 2008 Launch has been delayed to 2nd half of the year, but the Feb CTP has been released. The CTP is feature complete. The CTP include better functionality for Data compression, Policy based management enhancement. Integrated full text search engine and many more functionality.

Hi,

What do you do when you have to run some query in all the database in the server. Like To know all the relationship in all the database or to know the name of the table, procedure in the database in the server. The requirement can also be to know all the running process in all the database in the server etc.

In know all of us can write a small little curser after querying the sysdatabase for the all the database. But SQL server comes with a same purpose that is made for this purpose.

We an use the Stored procedure sp_Msforeachdb which takes a string (the query that should be run against all the database in the server.) Here is a way to use the stored procedure

EXEC sp_MSforeachdb 'USE ? SELECT name FROM sysobjects'

The question Mark (?) placeholder is used in place of the name of the database.

Hi,

One of the very simple and common that one of the commenter asked me a few days ago was why does the SQL query analyzer show only results unto 255 character only and not full column text. This is common to any data type in SQL server Query Analyzer.

The maximum number of character displayed using the tools->Option menu. Go to the result Tab and change the 'Maximum characters per column'. BY default the value is set to 255. The maximum value allowed here is 8192 character.

Hi,

An interesting question asked to me by one of my colleague quite some days back. How do we find all the SQL server running in the network in SQL? Well the requirement was unique, but the way to do it is pretty simple.

All we need to do is use the XP_CMDShell stored procedure in the master database with a parameter 'OSQL -L'. So the full code for the same is

CREATE PROCEDURE dbo.ListOFLocalServer 
AS 
BEGIN 
 
CREATE TABLE #tempserverTable(servername VARCHAR(255)) 
 
INSERT #tempserverTable EXEC master..XP_CMDShell 'OSQL -L' 
 
DELETE # tempserverTable WHERE servername ='Servers:' 
 
SELECT LTRIM(servername) FROM #tempserverTable WHERE servername!= 'NULL' 
 
 DROP TABLE #tempserverTable
END

Hi,

A few days ago one of my friend asked me about how to concatenate all the rows in a table or some of the rows in a table in a string in the SQL Server. The first idea was to create a user defined function, which would take the column name and return the concatenated value of all the rows in that column.

But a little later I found there is an inbuilt function in TSQL for the same. We can use the coalesce function to get the result of all the rows in a column in TSQL. Here is an example of the same

Hi,

When inserting a row in the database with an identity column as a primary key, most of the time we need to capture the new identity value generated. In SQL Server there cane as many as three approaches for the same.

            @@IDENTITY

            SCOPE_IDENTITY()

            IDENT_CURRENT(‘tablename’)

All of them can be used to find the last identity value inserted in the database, but they differ in the functionality depending on the scope or source of the insert as well as the connection that insert the row.

Hi,

Many a times we want to check if all the characters in a string are in capital letter or not? Doing this in Dot net is very simple. But what if this kind of checking neds to be done in SQL server. We all know SQL is not the best language to do these kind of checking.

Last Day I had to some similar kind of work. I created a small function to check if all the character in a string(username etc..) are in capital letter or not. Here is the function.

Hi,

In last few days I have been working with SQL Server Users. In My application the user of the Stored Procedures is not the creator (owner) of the Stored Procedures. Hence the user does not have execute permission on any of the Stored Procedures by default.

Of Late due to some reason we have to create many SQL Server user in the application. Whenever we create a new user in the SQL Server (the user account is used for accessing the database), we have to grant execute permission on all the SP’s. Doing this with the enterprise manager can be very troublesome as we have over 300 Stored Procedures.

I have created a small script with the help of which I get a string that gives permission to the new SQL Server User to all the Stored Procedures.

The script is simple.

select 'Grant Execute on ' + name +  ' SQLServerUSERName'
from sysobjects where xtype in ('P')

Hi,

It’s been quite a few days that SQL server 2008 (Katmai) CTP has came out. SQL server 2008 is being released with Visual studio 2008 and Longhorn server. There are many new features in the new version of SQL Server.

Here are some of the new features (Categorised) of the SQL server 2008.

Security and Data Auditing

Transparent Data Encryption

Data Auditing

Availability and Reliability

Hi,

Many a times we change some filed name or a table name in the database. This causes to change all the stored procedure that uses the field. Its can be very easily done if we can get the name of all the stored procedure contains the given text. (It can be bit difficult in case of column name as 2 table can have the same column name. So we need to track it with the help of the table name, unless we are sure that the column name is unique in the database.)

Here is a simple query that gives all the procedure names that contains the given text.

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
                

Hi

Yesterday I was writing a stored procedure, where I had to fetch data based on the Null condition. What I had to do was make a null checking in the field. If the value is null then I would take another column or else the current. The same logic would apply for multiple columns.

Hence in one query I had to check 5 columns to fetch the data. At first I had 2 things in mind, Use the case condition to fetch data. Another thing that came to my mind instantly was the isNull function. A simple example would be

Isnull(column1, Isnull(column2, Isnull(column3, Isnull(column4,0))))

Hi,

Last few days I have been working a lot in SQL server with DateTime data type and learned some simple but good tricks. One of the things that I learned was the month and year function. Normally when we have to find the month or year from a date variable or column we use the datepart function where we also need to pass what date part we need.

The month and year function do nothing special. They also return the month and year for the given date function. So what’s good about it? For me it only makes the query look easier to read and understand.

Hi

There are different types of lock in SQL Server 2000 and 2005. These locks are applied in different situations. Here is the list of locks and the situation for the locks.

SHARED - This lock is applied for read operation where the data is not updated. A good example would be the select statement.

UPDATE – This locked on those resources that can be updated. This lock prevents the common form of dead lock that occurs when multiple sessions are locking the data so that they can update it later.

EXCLUSIVE - Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.

Hi

An interesting question came up, when I was discussing some point on database isolation level with my colleague. The point was that to get a read – consistent view to all the transactions in the database what isolation should be there.

The point got more interesting when we found that there is a difference in how this works in SQL Server 2000 and SQL Server 2005.

In SQL server 2000 we get the read consistent view to all transaction in the database only in the SERIALIZABLE isolation level. Any other isolation level we can perceive uncommitted dependency (also called dirty reads), non-repeatable reads or Phantom reads.

Hi

There are many things that we want to do in SQL server which do not ship with the product but are still freely available. There are many free tools available for SQL server that are less known to the developers because they do not ship with the product. But these tools can be more than handy for many situations. And very importantly these tools are freely available from Microsoft.

Here is a list of tools available from Microsoft freely for SQL server 2000. [Note: These list is not exclusive. There are more tools available from Microsoft. Please check Microsoft’s Site for the same.]

Read80Trace - It is a command line utility for processing trace files generated by SQL Server 2000.

Hi

In SQL Server we can add day to date in two ways. We can use the DATEADD function to add date like

SELECT DATEADD(dd, 2, GETDATE())

This adds one day to the current date. We can also use the following query to get the current date and add a one-day to it. Both the queries will give same results.

SELECT GETDATE() + 2

If you are thinking that why does an + operator produces exactly same results? Read on.

As we know that the datetime data occupies 8 bytes (4 of them stores number of days after or before January 01 1900 and the other 4v store number of 3.33 ms clock tics since midnight. All the number are stored as integer data type.

Hi

In SQL server 2005 if we want to perform a bitwise exclusive or operation between two integers then we can use the ^(Shift + 6) character. The exact syntax would be like

Expression ^ Expression

For example 3 ^ 2

The statement would return true since one of the expression evaluates true. Note this only works with expression integer category data types, bit, binary and varbinary data types.

The operator performs logical or , taking each corresponding for both expressions. The bits in the result are set to 1 if only one bits in the input expressions have value one. If both bits are either 0 or 1 the bit in the result will be cleared to the value of 0.

In case the two expressions have different integer data types (smallint, tinyint or int), the smaller data type argument is converted to the larger data type

Thanks
Vikram

Hi

One major aspect of database design that has been debated a lot is having primary keys. Normally a primary key would be the entry point to the data to perform DML action. Not having primary keys are not just bad for design purpose, but that can also cause major problems in replication. The problem can be even severe when we are importing when we are trying to import data from other data source and there might be some duplicate data there.

But Lets say you have a database already designed and you feel there are some tables that do not have any primary key. You have over 200 tables in the database so to find out manually which table has primary key, which do not can be more than tedious. But there is an easy way out. You can query the system tables to find out these results. Below are two queries to provide the list of tables with and without primary key.

--Query to find all the tables with primary key

Hi

Yesterday I came across a very common but interesting requirement from a friend. Actually the requirement is very common for developer dealing with the database. Many a times a we have some SQL, TSQL code which we use during development but that should not run on the server.

Normally developers have a process of checking from the process prospective so that the code does not go on the server. But mistakes are inevitable. So we should also have a solution for the same from programmatic aspect. This is very important because some code (Like delete, or update statement) can cause big problem in the live site if they run by mistake also.

We should have a process to stop such error, but we can also take small care in the code itself to prevent any major problem. Some of the way are discussed below.

Hi,

A few days back a friend of mine asked me a simple but tricky question in SQL. The question was how would you check that the number of rows deleted through a delete query and the logic of the delete query is correct.

I know the first answer that comes to the mind the @@rowcount variable. But that variable is good enough for the row count only. How do you verify the login of the delete statement?

Hi

When working with Database, the most difficult and important thing is to ensure that the database is as fast as possible. After a few year when the number of users in a database grow and the data also start to increase, the performance starts to slide down. At this point it is very difficult to optimize the database for better performance.

This is reason enough that we should keep the performance in our mind from the very early stages of database life cycle. This is not very easy to do because of many factors like changing nature of business, unthought changes in database etc. But there are a few generic tips that can be followed for better performance of the database.

Hi

In SQL Server we have many option to format a date-time string. One of the very first needs is the current date-time. We use the getdate() function to get the current date-time. This is very much used in the process where we also store the date-time of the data insertion or editing in the database. The function provides date-time based on the server it is in.

But if you need a universal date-time then you need to use the getutcdate() function. But many a time we also need to change the format of the date-time, or convert it to a string and then change the format of the string. Here is a small list of the format that can be applied for the date-time.

Hi

We can configure the given database to update data asynchronously in SQL server 2005. This was not possible in SQL server 2000. In SQL Server 2000 we had to update the statistics synchronously. This is till the default in SQL server 20005.

If a given query request for the update of statistics without this option set, then the query will have to wait for till the statistics are updated and then be executed.

But if set the option for AUTO_UPDATE_STATISTICS_ASYNC then the query will not wait for the update of the statistics but will execute with the current statistics and at the same time a background process will start the automatic updation of the statistics as soon as possible. This will not prevent any query request.

Hi

Many a times we want to find the amount of space a particular database column is talking. We have the Len function in the SQL to get the length of a particular column data.

But the problem with this unction is that it does not work on the ntext, text or the image data type.

So when you have to find the length of a text, ntext or image data type use DATALENGTH function. This function can be used on all data types.

This is another of those functions that is not used daily but can be very helpful at difficult times.

Hope this helps
Thanks
Vikram

Hi

SQL server 2000 also has a WAITFOR keyword to suspend the execution of a connection for a given time interval or a specified day of time has reached. The WAITFOR command can be specified with either of the two clauses.

The delay keyword with the amount of time to be passed before the WAITFOR statement is completed. This time can be up to 24 hours. Or we can use the time keyword followed by a time to specify the completion of the WAITFOR statement.

Here are two example of using the statement.

WAITFOR DELAY '00:00:02'

SELECT * FROM CATEGORIES

This example delays the execution of the select statement by 2 seconds.

BEGIN

   WAITFOR TIME '23:00'

   DBCC CHECKALLOC

END

Sometimes we create table without any primary key and duplicate records are entered in the table. When we have two records in the database with no difference what so ever it’s problematic to distinguish between rows. In these case the other row should be deleted. In SQL Server 2000 we have to use the Rowcount to limit the number of rows affected by the command.

[Note: Remember that the default value is 0, which means that there is no limit of the number of rows returned]

But we can change the value before using the command. We should also remember to change the value back after we are done with the command. To delete 2 rows with same record we need to set the value of Rowcount to 1 and issue a delete command for the record.

Hi

While working with a self referencing table when we need to delete a record, we also need to delete all the records inside that category. Hence the delete should have a cascading effect on the records.

For this reason its best to create a self referencing function which will give back a table having all the child values. This function then can be used to get the entire child category list and delete them with and in statement.

For our example we are using a Self referencing NLevelCategry table which is like this

Hi

If you have a Self Referencing Table one of the major tasks is to get the parents of the given category. This will be required when you want to show the bread-crumb, and many other things.

For this reason its best to create a self referencing function which will give back a table having all the parent values. This function then can be used to get the entire parent category and work with it.

For our example we are using a Self referencing NLevelCategry table which is like this

Hi

Many a times we have to insert multiple data on click of one button. Like inserting all the checked records in the favorite table of the member. It is not a good idea to insert the records by using as many insert queries.

At these points we should insert all the records with only one call to the database. We can use a stored procedure that takes a comma (or any separator you want to use) list of values and insert them in the database. This way the whole process will be very fast and efficient.

In this example we will insert data in the MemberProductfaveriote table. We will be insert the member ID and the MemberProductId(all the product selected as favorite from the list). We will pass the MemberID and the comma separated list of MemberProductId.

The stored procedure will insert each of the record in the database.

Hi

Many a time we want to get random number in SQL server. There is an easy way to do this. Use the RAND function to generate a random number. Here is an example of the RAND Function

SELECT RAND()
Returns result 0.5144366554661576

What if we want a random number with each record in the result set. But the RAND function will give you same value if used (as a column) in a query. This is not what you want. So what is the solution?

We can create user-defined function that returns the Random values. This way we will get a random number for each record in the record-set.

We can also use the NEWID() function. The NEWID function returns a unique identifier for each row. The syntax to use this would be like

Hi

In SQL server 2000 there are 2 main keywords for deleting data – Truncate and delete. Although the end result might be same but both work very differently. We should take into consideration the advantages, limitation and the consequences when using one of them.

When we use the delete statement, SQL server deletes one row at a time. Each row is logged in the transaction LOG. This also means theta the server will also maintain the Log Sequence number. This will consume more database more resource in the database and the process will be slow. But this also gives an advantage. The transaction can be rolled back as there are transaction log.

Also you can use the where clause with the delete command but not with the truncate command. With truncate command it’s all records or nothing. Also one more advantage of the truncate command is that it also resets the identity seed of the table. Also the fact that deallocated pages is returned to the system for use in other areas.

Hi

Many a time we need a table with hierarchy data. We cannot define the level of the hierarchy at the beginning but is supposed to decide by the data. At these points its best to use a table which stores the hierarchy and display data from that table. One of the better hierarchy is example is the product catalog for an online store.

But SQL Server is a relational database, not a hierarchical database. So, we have to store the data in normalized, relational tables, but come up with programming techniques to process this data in a hierarchical manner. We do not have any built in support in SQL server 2000 for this.

To this we first create a  Category table

Hi

From time to time we all need to clear up dev server, truncate the log-file and resize the bloated LDF file of the SQL Server.

How to do it?

Suppose the name of the database is Vikram

First we make the server truncate the log the next time it runs the checkpoint.
EXEC sp_dboption 'Vikram', 'trunc. log on chkpt.', 'TRUE'

Now force a check point for the database
CHECKPOINT