Feeds:
Posts
Comments

Hi All,

In different kind of situation we may required to generate rownumber on a table. Here I have explained three methods to get the rownumber on a table.

You can decide yourself to find the best approach based on your requirement.

Method: 1

USING RANK()FUNCTION:

Rank function generates the rownumber based on the Order by column and it considers same rank for same value in the given order by column and it will consider actual row number as next rank.

Ex:

——–USING RANK()———

SELECT RANK() OVER (

ORDER BY Title) AS [RANK],

LoginID, ManagerID, Title, BirthDate, MaritalStatus, Gender

FROM [HumanResources].[Employee]

Method: 2

USING DENSE_RANK()FUNCTION:

DENSE_RANK () function generates the rownumber based on the Order by column and it considers same rank for same value in the given order by column and it will consider actual rank number as next rank.

Ex:

——–DENSE_RANK()———

SELECT DENSE_RANK() OVER (

ORDER BY Title) AS DENSE_RANK,

LoginID, ManagerID, Title, BirthDate, MaritalStatus, Gender

FROM [HumanResources].[Employee]

Method: 3

USING ROW_NUMBER() FUNCTION:

ROW_NUMBER () function generates the rownumber based on the Order by column and it considers identity number for the given order by column.

EX:

——–ROW_NUMBER()———

SELECT ROW_NUMBER() OVER (

ORDER BY Title) AS RowNumber,

LoginID, ManagerID, Title, BirthDate, MaritalStatus, Gender

FROM [HumanResources].[Employee]

Hi All,

      I recently came across the situvation like how to delete all the tables in the database?

     It’s interesting because a single line of statement can do this entire action. I thought to share this with you all.

      The solution is here:

         EXEC sp_MSforeachtable @command1 = “DROP TABLE ?”

 when i start looking further on the same i also idenitfies the following options also,

      To delete all records in all tables in the database use,

     EXEC sp_MSforeachtable @command1 = “DELETE FROM ?”

     To select all records in all tables in the database use,

     EXEC sp_MSforeachtable @command1 = “select * FROM ?”

     To Truncate all records in all tables in the database use,

     EXEC sp_MSforeachtable @command1 = “TRUNCATE TABLE ?”

Note:  The procedure sp_MSforeachtable is hidden in MSSQL

After publishing of my book “SQL Server Secret Diary” one of my friend has asked me a good question about decryption of encrypted objects in SQL Server.

I would like to share my answer to you all.That is in SQL server unfortunately there is no simple way to decrypt the code. And it is a hard to do this using codes.

But,there is a possible way to solve this problem is using “Third party tool”.There is number of tool are available in online to solve this problem.

I tried one of the third party tool called “DMT SQL Decryptor 3″  in following url http://www.bestshareware.net/downloading/dmt-sql-decryptor.htm

Using this tool you can easily get the encrypted objects like (Stored Procedure,View,Functions,..).

There are many ways to do this in SQL Server,Here i  will show you the one of the way to Convert a column of the table into Comma separated string using  FOR XML PATH in query.

Example:

CREATE TABLE
rowconcat
(
rowno INT PRIMARY KEY,
rowcode VARCHAR(30))
INSERT INTO rowconcat VALUES (1,’one’)
INSERT INTO rowconcat VALUES (2,’two’)
INSERT INTO rowconcat VALUES (3,’three’)
INSERT INTO rowconcat VALUES (4,’four’)
INSERT INTO rowconcat VALUES (5,’five’)

SELECT STUFF(
(SELECT ‘,’+rowcode  FROM rowconcat FOR XML PATH(”)),
1,1,”);

Output:

click on image to get better view

Technology blogs

We all know there are some system databases available in our SQL Server.But the purpose of these databases also we should know.

The following databases are available in SQL Server:

  • Master
  • Model
  • msdb
  • tempdb

Purpose Of Master Database:

When you create databases or any other objects, information about the objects is maintained inside the master database. The master database contains a set of system tables that track most of the objects on the server.

Purpose Of Model Database:

The model database is used as a template for all new user databases. Users do not use the model database. Template can include specific settings, security constructs and all sorts of useful stuff.

Purpose Of Msdb Database:

The msdb database contains information about tasks, alerts, and operators for both user-defined tasks and tasks related to replication.If the MSDB database is corrupted or damaged then scheduling information used by SQL Server Agent will be lost. This will result in the failure of all scheduled activities.

Purpose Of Tempdb Database:

The TempDB is recreated every time when ever SQL Server restarts. It stores all the temporary objects such as temporary tables, global temporary tables, temporary stored procedures, cursors, table variables information. You cannot take the the backup and recovery operation in TempDB database.


Generally, peoples may choose the  @@VERSION to get the version information, But using this we can’t get the service pack details.

To get Service Pack information use

SELECT SERVERPROPERTY(‘ProductLevel’) as SP

This will give the service pack level of the currently installed version of SQL Server.

Also, you can use

SERVERPROPERTY(‘EDITION‘) — To get the edition details of the currently installed version of SQL Server.

SERVERPROPERTY(‘PRODUCTVERSION‘) –To get the version details of the currently installed version of SQL Server.

There is lots of confusion regarding this questions that is the “Maximum Limits Specifications” of Parameters per stored procedure.

In Books Online it’s mentioned as 1,024 this is incorrect states. Actually the maximum number of parameters in a stored procedure is 2,100,the number of parameters from 1,024 was significantly increased from SQL Server 7.

The following screen shot shows you, that the CREATE PROCEDURE usp_MaxParam was trying with 2101 parameter but it was unsuccessful, sql server throws the error  “There are too many parameters in the CREATE PROCEDURE statement.The maximum number is 2100.”

(For better view click on image.)

Follow

Get every new post delivered to your Inbox.