Advertisements

Archive

Archive for the ‘SQL’ Category

SQL Query for truncate all tables in a database in SQL/SQL Azure

September 26, 2011 2 comments

How to delete or truncate all the data from all the tables in a database

 For clean up the database or switch to a new database, we may need to truncate or delete all tables in the database. Its not easy to select and delete all tables in a database as the database may having n number of tables. We can get all tables in a database from sys objects and we can apply delete or truncate script to each and every table from a single cursor.

 SQL script for delete/truncate all tables in a database in SQL/SQL Azure

 The following script truncates all tables in the selected database. If you want to delete all tables in the database we can edit this query with delete statement instead of truncate statement.

 DECLARE @GetObjectName AS CURSOR 
DECLARE @StringVal AS nvarchar(max) 
DECLARE @DBObjName AS nvarchar(100)
DECLARE @Type as Varchar(2)

SET @GetObjectName = CURSOR FOR 
Select type,Name from sys.sysobjects where type in('U')
OPEN @GetObjectName 
FETCH NEXT 
FROM @GetObjectName INTO @Type,@DBObjName

WHILE @@FETCH_STATUS = 0 
BEGIN
     Set @StringVal = 'truncate table ' + @DBObjName
       exec (@StringVal)           
FETCH NEXT 
FROM @GetObjectName INTO @Type,@DBObjName
END 
CLOSE @GetObjectName 
DEALLOCATE @GetObjectName

 How to drop all stored procedures (sps) and functions from a database

The following script drop all procedures and user defined functions from a database.

 DECLARE @GetObjectName AS CURSOR 
DECLARE @StringVal AS nvarchar(max) 
DECLARE @DBObjName AS nvarchar(100)
DECLARE @Type as Varchar(2)

SET @GetObjectName = CURSOR FOR 
Select type,Name from sys.sysobjects where type in('P','FN','TF')
AND Name not in ('DBM_EnableDisableAllTableConstraints')
OPEN @GetObjectName 
FETCH NEXT 
FROM @GetObjectName INTO @Type,@DBObjName

WHILE @@FETCH_STATUS = 0 
BEGIN

      IF @Type='P'
      BEGIN
            Set @StringVal = 'Drop Procedure ' + @DBObjName
            exec (@StringVal)
      END
      ELSE IF @Type='FN' OR @Type='TF'
      BEGIN
            Set @StringVal = 'Drop Function ' + @DBObjName
            exec (@StringVal)
      END

FETCH NEXT 
FROM @GetObjectName INTO @Type,@DBObjName
END 
CLOSE @GetObjectName 
DEALLOCATE @GetObjectName
Advertisements

How to find the size and cost of sql azure database

September 15, 2011 Leave a comment

SQL query to find the cost and size of sql azure database

 SQL  Azure database is a paid service as per the usage of the database. So we should have an idea regarding our usage of data in the sql azure database. Then only we can use it as cost effective product. So we need to regularly checking the size and cost of our sql azure database.

 Query to find the size and cost of SQL azure entire database

 The  following query will return the size and cost of the sql azure database.  The latest price of the azure storage can be seen here. http://www.microsoft.com/windowsazure/pricing/

 The following query will give the size and price of the database as per the following price:

 

DECLARE @SizeInBytes bigint
SELECT @SizeInBytes =
(SUM(reserved_page_count) * 8192)
    FROM sys.dm_db_partition_stats

DECLARE @Edition sql_variant
SELECT  @Edition =DATABASEPROPERTYEX ( DB_Name() , 'Edition' )

SELECT    (CASE
    WHEN @SizeInBytes/1073741824.0 < 1 THEN
(CASE @Edition WHEN 'Web' THEN 9.99 ELSE 99.99 END)
    WHEN @SizeInBytes/1073741824.0 < 5 THEN
(CASE @Edition WHEN 'Web' THEN 49.95 ELSE 99.99 END)
    WHEN @SizeInBytes/1073741824.0 < 10 THEN 99.99 
    WHEN @SizeInBytes/1073741824.0 < 20 THEN 199.98
    WHEN @SizeInBytes/1073741824.0 < 30 THEN 299.97            
    WHEN @SizeInBytes/1073741824.0 < 40 THEN 399.96             
    WHEN @SizeInBytes/1073741824.0 < 50 THEN 499.95            
         END)  / @SizeInBytes
 

Query to find size and cost of SQL Azure datbase as per indexes

 The following query will return the size and cost of sql azure dtabase’s indexes.

 DECLARE @SizeInBytes bigint
SELECT @SizeInBytes =
(SUM(reserved_page_count) * 8192)
    FROM sys.dm_db_partition_stats

DECLARE @Edition sql_variant
SELECT  @Edition =DATABASEPROPERTYEX ( DB_Name() , 'Edition' )

DECLARE @CostPerByte float

SELECT    @CostPerByte = (CASE
    WHEN @SizeInBytes/1073741824.0 < 1 THEN
(CASE @Edition WHEN 'Web' THEN 9.99 ELSE 99.99 END)
    WHEN @SizeInBytes/1073741824.0 < 5 THEN
(CASE @Edition WHEN 'Web' THEN 49.95 ELSE 99.99 END)
    WHEN @SizeInBytes/1073741824.0 < 10 THEN 99.99 
    WHEN @SizeInBytes/1073741824.0 < 20 THEN 199.98
    WHEN @SizeInBytes/1073741824.0 < 30 THEN 299.97            
    WHEN @SizeInBytes/1073741824.0 < 40 THEN 399.96             
    WHEN @SizeInBytes/1073741824.0 < 50 THEN 499.95            
         END)  / @SizeInBytes SELECT idx.name,
SUM(reserved_page_count) * 8192 'bytes',
      (SUM(reserved_page_count) * 8192) * @CostPerByte 'cost'
FROM sys.dm_db_partition_stats AS ps
    INNER JOIN sys.indexes AS idx ON
idx.object_id = ps.object_id AND idx.index_id = ps.index_id
WHERE type_desc = 'NONCLUSTERED'
GROUP BY idx.name
ORDER BY 3 DESC
 

Query to find out the size and cost of each tables in SQL Azure database

 The following query return the cost per month per row for every table in the database.
DECLARE @SizeInBytes bigint
SELECT @SizeInBytes =
(SUM(reserved_page_count) * 8192)
    FROM sys.dm_db_partition_stats
DECLARE @Edition sql_variant
SELECT  @Edition =DATABASEPROPERTYEX ( DB_Name() , 'Edition' )
DECLARE @CostPerByte float
SELECT    @CostPerByte = (CASE
    WHEN @SizeInBytes/1073741824.0 < 1 THEN
(CASE @Edition WHEN 'Web' THEN 9.99 ELSE 99.99 END)
    WHEN @SizeInBytes/1073741824.0 < 5 THEN
(CASE @Edition WHEN 'Web' THEN 49.95 ELSE 99.99 END)
    WHEN @SizeInBytes/1073741824.0 < 10 THEN 99.99 
    WHEN @SizeInBytes/1073741824.0 < 20 THEN 199.98
    WHEN @SizeInBytes/1073741824.0 < 30 THEN 299.97            
    WHEN @SizeInBytes/1073741824.0 < 40 THEN 399.96             
    WHEN @SizeInBytes/1073741824.0 < 50 THEN 499.95            
         END)  / @SizeInBytes
SELECT    
      sys.objects.name,
      sum(reserved_page_count) * 8192 'Bytes',
      row_count 'Row Count', 
      (CASE row_count WHEN 0 THEN 0 ELSE
       (sum(reserved_page_count) * 8192)/ row_count END)
        'Bytes Per Row',
      (CASE row_count WHEN 0 THEN 0 ELSE
       ((sum(reserved_page_count) * 8192)/ row_count)
        * @CostPerByte END)
        'Monthly Cost Per Row'
FROM    
      sys.dm_db_partition_stats, sys.objects
WHERE    
      sys.dm_db_partition_stats.object_id = sys.objects.object_id
GROUP BY sys.objects.name, row_count

How to implement leading zero to the variable in the SQL Server/Azure

September 7, 2011 1 comment

Leading zero to the variable in SQL Server

To implement leading zero to the variable in SQL Server 2008, we can use following script. In some scenario we have to leading zero to the selected values from SQL server table as per the number of digits in the selected value.

 Create a table in SQL Server  

CREATE TABLE Employee(Code INT);

We can insert values in to the age column of the Employee table
    INSERT Employee VALUES(’13’);
    INSERT Employee VALUES(‘111′);
    INSERT Employee VALUES(’12’);
    INSERT Employee VALUES(‘322’);
    INSERT Employee VALUES(‘422’);

1 row(s) affected.
1 row(s) affected.
1 row(s) affected.
1 row(s) affected.
1 row(s) affected.

 Select Code from Employee table

 SELECT * FROM Employee;

 Code
13
111
12
322
422

5 row(s) affected.

 How to Format column value foor leading zero in SQL Server

Now we are going to implement leading zero to each code up to the 4 digits.

 SELECT RIGHT('0000'+ CONVERT(VARCHAR,Code),4) AS EmployeeCode FROM Employee;

 EmployeeCode
0013
0111
0012
0322
0422

 If we want to format with 6 digits can use following scripts

SELECT RIGHT('000000'+ CONVERT(VARCHAR,Code),6) AS EmployeeCode FROM Employee;

How to Import CSV File Into SQL Server Using Bulk Insert in SQLServer?

July 26, 2011 12 comments

Restore data from csv files into SQL Server

This is very common scenario that we have to import data into sql server database from csv files. In SQL Server there is an option to insert data from a csv file into database. We are going to demonstrate how we can import data from csv files into a sql server database. The constraints are we need to have data in csv files as same structure in the table structure

BULK INSERT

Imports a data file into a database table or view in a user-specified format in SQL Server 2008 R2. Use this statement to efficiently transfer data between SQL Server and heterogeneous data sources.

Arguments :

database_name

Is the database name in which the specified table or view resides. If not specified, this is the current database.

schema_name

Is the name of the table or view schema. schema_name is optional if the default schema for the user performing the bulk-import operation is schema of the specified table or view.

table_name

Is the name of the table or view to bulk import data into. Only views in which all columns refer to the same base table can be used. For more information about the restrictions for loading data into views,

data_file

Is the full path of the data file that contains data to import into the specified table or view. BULK INSERT can import data from a disk (including network, floppy disk, hard disk, and so on).

BATCHSIZE =batch_size

Specifies the number of rows in a batch. Each batch is copied to the server as one transaction

CHECK_CONSTRAINTS

Specifies that all constraints on the target table or view must be checked during the bulk-import operation.

Steps to Import CSV to SQL Server / Sample CSV import to SQL Server using BULK Insert

Here we are going to show how to import csv files data into a SQL Server table using bulk Insert method. First of all create a csv file with data as follows :- id, name, age, joindate (Should be same as structure of table).  We have a table with same structure of columns : id, name, age , joindate.

Step 1 : Create a table

 

CREATE TABLE [dbo].[Employee](
      [id] [int] IDENTITY(1,1) NOT NULL,
      [name] [varchar](50) NULL,
      [age] [int] NULL,
      [joindate] [datetime] NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
      [id] ASC
)
) ON [PRIMARY]
 

Step 2 : Create a csv files and enter the data as same as structure table

 

Step3 : Run following script to insert data from csv to table

BULK
INSERT Employee
FROM 'c:\emp.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

Nested Common Table Expression(CTE) in SQL Server 2005/2008

June 28, 2011 10 comments

What is Common Table Expression (CTE)?

A common table expression (CTE) is a temporary storage result set, which will be accessible within the next execution scope of a query. That means we didn’t get CTE result after the second query statement.  A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

A very simple example of Common Table Expression (CTE) in SQL Server for pagination

Suppose we have a table ‘Employee’ with following structure. Suppose we need to apply pagination for this table, that means fetch only some part of data within the given index.  To achieve this, we usually insert all records into a temporary table to get a new id column as row number. Then we have to fetch data from this table as per this new column.

Table Structure:

By using Common Table Expression (CTE) we can accomplish pagination query with single statement as follows. In our CTE we are fetching data with row number, and from this CTE we are fetching some range of data as we needed. This query helps to get pagination data in SQL Server using CTE with single execution of query. The scope of the CTE will be ended just after this execution of statement.

WITH CTE AS
(
SELECT id
,name
,age
,joindate
,ROW_NUMBER() OVER (ORDER BY id DESC) AS RowNumber
FROM employee
)
SELECT *
FROM CTE
WHERE RowNumber BETWEEN 1 AND 5;

OUTPUT:

 

Is it possible Nested Common Table Expression (CTE) in SQL Server?

In some scenario we need to do some calculation from the result of first CTE and have to generate another result from the first CTE. For doing that, can we do CTE inside another CTE?  To accomplish this we can try with nested CTE in SQL Server which means declare a new CTE just after the first CTE to use result of first CTE.  Suppose we need to get number of years of experience of each employee. If we have very large number of data in the table, it will be very slow if we calculate this much of employee’s service years (If we have to fetch only 5 at a time).

In this case what is the better method is, first of all fetch the 5 records (pagination size) from the table, and apply the all calculation that we need to do for these 5 records only. We can accomplish this technique using CTE. What we are going to do is, first fetching 5 records from the table using CTE1, and then the very next step we are creating next CTE2. In CTE2 we are doing all calculation which we needed with result of CTE1.  Then it will be a faster pagination in SQL Server even having any complex calculation in the query. This nested CTE mechanism can be applied for anywhere in the SQL Server if we have any more complex calculation have to be done from the previous results. We can n number of CTE in nested CTE SQL Server 2005/2008. The last CTE can use previous CTE result, which can be used just previous CTE result and so on.

;with CTE1 as (
SELECT id
,name
,age
,joindate
,ROW_NUMBER() OVER (ORDER BY id DESC) AS RowNumber
FROM employee    
)
,CTE2 AS (
SELECT CTE1.id
,CTE1.name
,CTE1.age
,CTE1.joindate
,CTE1.RowNumber
,DATEDIFF(YEAR,CTE1.joindate,GETDATE()) as yearOfservce
FROM employee INNER JOIN CTE1
ON employee.id = CTE1.id
WHERE CTE1.RowNumber between 1 and 5
)
SELECT * FROM CTE2;

OUTPUT:

 

Why need to use Common Table Expression (CTE) for pagination query?

In some scenario there is some complex calculation have to do for the fetching queries. In such case if we do this calculation for all data in the table and fetch only paginated rows finally, there is some performance issue will be there and we didn’t get proper useful for using pagination. For this scenario we have to fetch paginated row from the table and do the complex calculation to those paginated rows only, it will improve the performance of pagination query. We can achieve it same as above queries using CTE (Common Table Expression) in SQL Server 2055/2008 for pagination. Common Table Expression (CTE) improve the performance of the pagination query in SQL Server as it takes only needed data first then do the calculation as above query.

Pagination Stored Procedure in SQL

May 24, 2011 1 comment

When listing large amount of data from database, we have to face performance issue in ASP.net pages. There is a default paging mechanism available for some listing controls like Gridview, Listview etc. But all of them have to get total amount of data for manipulation, and from that data they are listing small amount of data using paging mechanism.

 That means, if there are 1 crore rows of data in the table, first of all we need to query out this all records and set to listing control. So it will take very long time to query out this amount of data from the table. 

Better method of pagination in SQL 

So the better mechanism for pagination is, we need to query only small amount of data that we need to display to the user at a time. In order to fetch this function, we have to create a store procedure in SQL with start and end row as parameter. Then If we pass 21 and 30 to the following procedure it will return only 21 to 30 (10 records) and total number of records. So it will be very fast mechanism for pagination.

Pagination Stored Procedure in SQL

 

CREATE PROCEDURE [dbo].[scheduler_view_syncjobs_paging]
@fromRow INT,
@toRow INT
AS
BEGIN

WITH SyncJobs AS
(
      SELECT coalesce(instance.Schedule_Time,
scope.ScheduleTime,getdate()) as ScheduleTime
    ,scope.ScopeName
    ,instance.Status
      ,instance.Schedule_Time as starttedTime
      ,logs.logDetails
      ,ROW_NUMBER() OVER (ORDER BY scope.ScopeID DESC) AS 'RowNumber'
      FROM scheduler_scope scope
      LEFT JOIN scheduler_SyncInstance instance on 
instance.ScopeId=scope.Scopeid
      LEFT JOIN scheduler_logs logs on 
logs.InstanceID=instance.InstanceId
)
SELECT Count(*) as TotalCount
FROM SyncJobs;

WITH SyncJobs AS
(
      SELECT coalesce(instance.Schedule_Time,
scope.ScheduleTime,getdate()) as ScheduleTime
    ,scope.ScopeName
    ,instance.Status
      ,instance.Schedule_Time as starttedTime
      ,logs.logDetails
      ,ROW_NUMBER() OVER (ORDER BY scope.ScopeID DESC) AS 'RowNumber'
      FROM scheduler_scope scope
      LEFT JOIN scheduler_SyncInstance instance on 
instance.ScopeId=scope.Scopeid
      LEFT JOIN scheduler_logs logs on 
logs.InstanceID=instance.InstanceId
)
SELECT *
FROM SyncJobs
WHERE RowNumber BETWEEN @fromRow AND @toRow;

END

GO
Categories: SQL Tags:

How to find out the tables and columns having particular data in SQL?


How to find out the tables and columns that hold the particular data

 In some of the scenario a developer have to check is there any particular value is stored in the any of the table in the entire db. It is very hard to find out the column and table that holds the particular data, by calling select query for each and every table in the database.

Following store procedure will search the particular data that we are given as the paramaeter to stored procedure, in the all tables and coloumns in the entire db and return the results. If we want to perfect match of the search key given the second parameter as 1 else 0.

 StoredProcedure for search a keyword in the entire database.

CREATE PROCEDURE FindMyData_String
    @DataToFind NVARCHAR(4000),
    @ExactMatch BIT = 0
AS
SET NOCOUNT ON

DECLARE @Temp TABLE(RowId INT IDENTITY(1,1),
SchemaName sysname,
TableName sysname,
ColumnName SysName,
DataType VARCHAR(100), DataFound BIT)

INSERT  INTO @Temp(TableName,SchemaName, ColumnName, DataType)
SELECT  C.Table_Name,C.TABLE_SCHEMA, C.Column_Name, C.Data_Type
FROM    Information_Schema.Columns AS C
        INNER Join Information_Schema.Tables AS T
            ON C.Table_Name = T.Table_Name
    AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
WHERE   Table_Type = 'Base Table'
        And Data_Type In
        ('ntext','text','nvarchar','nchar','varchar','char')

DECLARE @i INT
DECLARE @MAX INT
DECLARE @TableName sysname
DECLARE @ColumnName sysname
DECLARE @SchemaName sysname
DECLARE @SQL NVARCHAR(4000)
DECLARE @PARAMETERS NVARCHAR(4000)
DECLARE @DataExists BIT
DECLARE @SQLTemplate NVARCHAR(4000)

SELECT  @SQLTemplate = CASE WHEN @ExactMatch = 1
    THEN 'If Exists(Select *
                  From   ReplaceTableName
                  Where  Convert(nVarChar(4000), [ReplaceColumnName])
                               = ''' + @DataToFind + '''
                  )
             Set @DataExists = 1
         Else
             Set @DataExists = 0'
    ELSE 'If Exists(Select *
                  From   ReplaceTableName
                  Where  Convert(nVarChar(4000), [ReplaceColumnName])
                               Like ''%' + @DataToFind + '%''
                  )
             Set @DataExists = 1
         Else
             Set @DataExists = 0'
    END,
    @PARAMETERS = '@DataExists Bit OUTPUT',
    @i = 1

SELECT @i = 1, @MAX = MAX(RowId)
FROM   @Temp

WHILE @i <= @MAX
BEGIN
    SELECT  @SQL = REPLACE(REPLACE(@SQLTemplate, 'ReplaceTableName',
    QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)),
    'ReplaceColumnName', ColumnName)
    FROM    @Temp
    WHERE   RowId = @i

    PRINT @SQL
    EXEC SP_EXECUTESQL @SQL, @PARAMETERS, @DataExists = @DataExists OUTPUT

    IF @DataExists =1
        UPDATE @Temp SET DataFound = 1 WHERE RowId = @i

    SET @i = @i + 1
END

SELECT  SchemaName,TableName, ColumnName
FROM    @Temp
WHERE   DataFound = 1
GO
Categories: SQL Tags:
%d bloggers like this: