Wednesday, 22 August 2012

update with variable and stop at some predefined target

When using update with variables, it seem the variable is always evaluated first before any other filed.
Also it will not respect the condition using variable with the where clause. Put another way, no, you ca not use variable to dynamically control the data being updated. One workaround is to set condition inside set section.
For example. Try this:


USE [Test]
GO

/****** Object:  Table [dbo].[UpdateTest]    Script Date: 8/22/2012 10:02:10 AM ******/
if (exists (select 1 from sys.Tables where name='UpdateTest'))
    DROP TABLE [dbo].[UpdateTest]
GO

/****** Object:  Table [dbo].[UpdateTest]    Script Date: 8/22/2012 10:02:10 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[UpdateTest](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [count] [int] NOT NULL,
    [value] [int] NOT NULL,
 CONSTRAINT [PK_UpdateTest] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


SET IDENTITY_INSERT [dbo].[UpdateTest] ON

GO
INSERT [dbo].[UpdateTest] ([id], [count], [value]) VALUES (1, 5, 0)
GO
INSERT [dbo].[UpdateTest] ([id], [count], [value]) VALUES (2, 12, 0)
GO
INSERT [dbo].[UpdateTest] ([id], [count], [value]) VALUES (3, 20, 0)
GO
INSERT [dbo].[UpdateTest] ([id], [count], [value]) VALUES (4, 1, 0)
GO
INSERT [dbo].[UpdateTest] ([id], [count], [value]) VALUES (5, 5, 0)
GO
INSERT [dbo].[UpdateTest] ([id], [count], [value]) VALUES (6, 98, 0)
GO
INSERT [dbo].[UpdateTest] ([id], [count], [value]) VALUES (7, 98, 0)
GO
SET IDENTITY_INSERT [dbo].[UpdateTest] OFF
GO


declare @sum int, @TagetHitTimes int
set @sum=0
set @TagetHitTimes=0

--update [UpdateTest] set val1=0

--select * from [UpdateTest]

update [UpdateTest]
set  
    -- variable seems to be computed first
    -- only add sum when target is not achived
    -- stop adding when target is achived
    @sum=case when @sum>24 then @sum else @sum+[count] end
    -- used to track the first time target is achived
    ,@TagetHitTimes=@TagetHitTimes+(case when @sum>24 then 1 else 0 end)
    ,[value]=case when  @TagetHitTimes<=1 then [value]+@sum else 0 end

select * from [UpdateTest]
Posted by at No comments:

Tuesday, 14 August 2012

temp table vs table varialbe

(http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html)
table varialbe:
  • has some limitation compared to temp table
  • has less recompilation
  • not good choice for large data (row number is always 1 and no stats is available)
  • can be used with DMV tables
  • etc

Posted by at No comments:

Missing Index From SQL Server MDV

(From
http://blogs.msdn.com/b/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx
)



SELECT

    migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure

    ,db_name(mid.database_id) as DatabaseName

    ,object_name( mid.[object_id], mid.database_id) as TableName

    ,'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)

    + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'

    + ' ON ' + mid.statement

    + ' (' + ISNULL (mid.equality_columns,'')

    + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END

    + ISNULL (mid.inequality_columns, '')

    + ')'

    + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement

    ,migs.*

    , mid.[object_id]

    FROM    sys.dm_db_missing_index_groups mig

            INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle

            INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle

    WHERE    migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10

    ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC
Posted by at No comments:

Friday, 29 June 2012

searching text inside sql server sp/function/table

-- this works for SQL 2000 and better
SELECT DISTINCT
    o.xtype
    ,o.name
FROM    syscomments c
        INNER JOIN sysobjects o ON c.id=o.id
WHERE    c.TEXT LIKE '%transaction%'
order by 1,2

for sql server 2005+, use sys.procedures and OBJECT_DEFINITION:
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Employee%'
Posted by at No comments:

Thursday, 28 June 2012

Using Transparent Data Encryption Feature of SQL Server 2008 Read more: http://www.mytechmantra.com/LearnSQLServer/Using_Transparent_Data_Encryption_Feature_of_SQL_Server_2008_P1.html#ixzz1z6OM9Wlh

I found out this article very handy and I copied from

http://www.mytechmantra.com/LearnSQLServer/Using_Transparent_Data_Encryption_Feature_of_SQL_Server_2008_P1.html

Using Transparent Data Encryption Feature of SQL Server 2008

By: Editor
March 01, 2010

Introduction

Transparent Data Encryption is a new feature in SQL Server 2008. The TDE feature provides real time encryption of both data and log files. Encryption basically working in the following way; initially the data is encrypted before it's being written to the disk and it is decrypted before it is being read from the disk. When you are using the Transparent Data Encryption feature of SQL Server 2008 the encryption is performed by the SQL Server 2008 Database Engine and the SQL Server clients will not be aware of this change. However, before implementing this feature in Production environment I would request you to validate the solution completely in the Test Environment.
To enable Transparent Data Encryption Feature of SQL Server 2008 on a database, the DBA needs to perform the below mentioned four steps as described in Books Online:-
1. Create a master key
2. Create or obtain a certificate protected by the master key
3. Create a database encryption key and protect it by the certificate
4. Set the database to use encryption

Create a Master Key

The initial step will be to identify if there is any Master Key already created in the Instance of SQL Server 2008 where you want to implement this feature. You can verify the same by executing the below mentioned TSQL code. 
USE master
GO
SELECT * FROM sys.symmetric_keys WHERE name LIKE '%MS_DatabaseMasterKey%'
GO
If there are no records found, then it means there was no predefined Master Key on the SQL Server 2008 Instance. To create a Master Key, you can execute the below mentioned TSQL code. 
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'pass@word1'
GO

Create or obtain a certificate protected by the Master Key

Once Master Key is created then the next step will be to Create or obtain a certificate protected by the master key. This can be achieved by executing the below mentioned TSQL code.
Use master
GO
CREATE CERTIFICATE TDECertificate WITH SUBJECT = 'SQL Server TDE Certificate'
GO

/* Verify Certificate */
SELECT * FROM sys.certificates where [name] = 'TDECertificate'
 

Create a database encryption key and protect it by the certificate

Next step will be to create a new database. Once the database is created you can create a database encryption key and protect it by the certificate by executing the below mentioned TSQL code.
Use master GO CREATE DATABASE TryEncryption GO Use TryEncryption GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE TDECertificate GO Once the Database Encryption Key is created successfully you need to take a backup of the Certificate and the Private Key by executing the below mentioned TSQL code.
BACKUP CERTIFICATE TDECertificate TO FILE = 'D:\TDE\TDECertificate.cert' WITH PRIVATE KEY ( FILE = 'D:\TDE\EncryptPrivateKey.key', ENCRYPTION BY PASSWORD = 'Certific@tePass@word') GO

Set the database to use encryption

The final step will be to enable encryption on the user database by executing the below mentioned TSQL code.
ALTER DATABASE TryEncryption SET ENCRYPTION ON GO

Verify Database Encryption Status

You can verify the database encryption status by executing the below mentioned TSQL code.
SELECT DB_NAME(database_id) AS DatabaseName ,Encryption_State AS EncryptionState ,key_algorithm AS Algorithm ,key_length AS KeyLength FROM sys.dm_database_encryption_keys GO SELECT NAME AS DatabaseName ,IS_ENCRYPTED AS IsEncrypted FROM sys.databases where name ='TryEncryption' GO

Advantages of Transparent Data Encryption

1. Physical Security of Database Files
2. When Transparent Database Encryption feature is used all the backups of the TDE enabled database are encrypted

Disadvantages of Transparent Data Encryption

1. As Encryption is CPU intensive and it is performed at I/O level, any server with higher I/O and higher CPU load should avoid using this feature
2. This feature is only available in Enterprise and Developer Editions of SQL Server 2008
3. TDE encrypted database cannot be attached or restored in other edition of SQL Server 2008
4. If the certificate is lost then the data will be unreadable. Hence you need to protect the certificate and master key along with the database backup files
5. If you are using FILESTREAM feature, then be informed that only FILESTREAM enabled database is encrypted and not the actual files which are residing on the servers file system will be encrypted
6. There won't be much of a benefit if you planning to use Database Backup Compression feature of SQL Server 2008
7. As TempDB database is automatically encrypted once you have enabled encryption on any of the user databases. This resulted in slow query performance for non encrypted databases which may use TempDB 
 GO
Posted by at No comments:

Thursday, 21 June 2012

Find tables without primary key and the number of rows for SQL Server

USE [Database Name];
GO
SELECT    SCHEMA_NAME(schema_id) AS SchemaName
        ,name AS TableName
        FROM    sys.tables
        WHERE    OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0
                -- is there a better way to fileter out system tables?
                and name not like 'sys%'
                and name not like 'MSmerge%'
                and name not like 'MSrepl%'
                and name not like 'dpyDeployment%'
                and name not in ('dbmaintain_scripts')
        ORDER BY SchemaName, TableName;
GO

SELECT    SCHEMA_NAME(t.schema_id) AS SchemaName
        ,st.row_count as NumberOfRows
        ,t.name AS TableName
        FROM    sys.tables t inner join sys.dm_db_partition_stats st
                on t.object_id = st.object_id
        WHERE    OBJECTPROPERTY(t.OBJECT_ID,'TableHasPrimaryKey') = 0
                -- is there a better way to fileter out system tables?
                and t.name not like 'sys%'
                and t.name not like 'MSmerge%'
                and t.name not like 'MSrepl%'
                and t.name not like 'dpyDeployment%'
                and t.name not like 'MSpeer%'
                and t.name not in ('dbmaintain_scripts', 'MSdynamicsnapshotjobs', 'MSpub_identity_range')
                --and st.row_count>100
                and st.index_id < 2
        ORDER BY SchemaName, NumberOfRows desc--TableName;
GO
 SELECT    OBJECT_NAME(OBJECT_ID) TableName
        , st.row_count as NumberOfRows
        FROM    sys.dm_db_partition_stats st
        WHERE    index_id < 2
GO



SELECT    SCHEMA_NAME(t.schema_id) AS SchemaName

        ,st.row_count as NumberOfRows

        ,t.name AS TableName

        FROM    sys.tables t inner join
                    -- we have partitions for tables
                    (
                    select  
                        object_id
                        , sum(row_count) as row_count
                        from sys.dm_db_partition_stats
                        where index_id < 2
                        group by object_id      
                        --order by object_id
                    ) st

                on t.object_id = st.object_id

        WHERE    OBJECTPROPERTY(t.OBJECT_ID,'TableHasPrimaryKey') = 0

                -- is there a better way to fileter out system tables?

                and t.name not like 'sys%'

                and t.name not like 'MSmerge%'

                and t.name not like 'MSrepl%'

                and t.name not like 'dpyDeployment%'

                and t.name not like 'MSpeer%'
                and t.name not like 'MSsavedfor%'

                and t.name not in ('dbmaintain_scripts', 'MSdynamicsnapshotjobs', 'MSpub_identity_range', 'MSsubscription_agents', 'MSsnapshotdeliveryprogress')

                --and st.row_count>100

                --and st.index_id < 2

                -- further check index

                and --t.name not in

                t.object_id not in

                    (

                    select distinct --OBJECT_NAME(i.id),

                        id

                        from sysindexes i

                        WHERE    (i.indid BETWEEN 1 AND 254)

                                -- leave out AUTO_STATISTICS:

                                AND (i.Status & 64)=0

                                -- leave out system tables:

                                AND OBJECTPROPERTY(i.id, 'IsMsShipped') = 0

                    )

        ORDER BY SchemaName, NumberOfRows desc--TableName;GO

Posted by at No comments:

Friday, 8 June 2012

How To Obtain The Size Of All Tables In A SQL Server Database


-- copied from http://therightstuff.de/CommentView,guid,df930155-f60f-4f56-ab33-f1352ff091a1.aspx
SET NOCOUNT ON

DBCC UPDATEUSAGE(0)

-- DB size.
EXEC sp_spaceused

-- Table row counts and sizes.
CREATE TABLE #t
(
    [name] NVARCHAR(128),
    [rows] CHAR(11),
    reserved VARCHAR(18),
    data VARCHAR(18),
    index_size VARCHAR(18),
    unused VARCHAR(18)
)

INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''

SELECT *
FROM   #t

-- # of rows.
SELECT SUM(CAST([rows] AS int)) AS [rows]
FROM   #t

DROP TABLE #t
Posted by at No comments:
Subscribe to: Posts (Atom)