The Young DBA

Hello Friends,  Recently I have been shared My Post on Change Tracking Log  (winlinuxhelp.com) described how I am storing day to day Database schema change logs Of all server for Reporting &  System Analysis purpose. I want to continue writing about this Topic ..  So today I will share about Change Alert which I have been configured into  system  to alert on every schema changes into my cell. To Create run below Script Create TRIGGER safety1 ON DATABASE FOR CREATE_TABLE , ALTER_TABLE , DROP_TABLE , CREATE_PROCEDURE , ALTER_PROCEDURE , DROP_PROCEDURE , CREATE_VIEW , ALTER_VIEW , DROP_VIEW AS declare @object int                       declare @hr int       declare @rcnum varchar ( 200 ) declare @str varchar ( 200 )                SELECT @str = EVENTDATA (). value ( '(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]' , 'nvarchar(max)' ) --print @str exec @hr = sp_OACreate 'jmail.smtpmail', @object OU

When   we   installation or upgrade Sql Server   from a previous version   into 2008, by default a job will be created syspolicy_purge_history.   What does it do? Sql Server 2008 introduced a new feature called Policy Based Management.  For example, one could define a policy that says all tables must have a clustered index.  Once defined, the policy can be evaluated against targets to check for compliance. Each evaluation of compliance will be recorded in below tables :- 1. msdb, 2. syspolicy_policy_execution_history_details_internal,  3. syspolicy_policy_execution_history_internal, and  4. syspolicy_policy_category_subscriptions_internal  The evaluation condition is built on top of object facets, which is predefined by Microsoft. The purpose of that job, syspolicy_purge_history, is to remove records in those tables to maintain so it does take up space unnecessarily .  Happy Readding. @Sumnjha

Sometimes there would be a requirement to add serial number (or) row number to the result set. Example: The requirement would be to select some columns from the database tables along with Serial Number like below SNo Customer_Name Customer_Phone_Number 1 aaa xxx-xxx-xxxx 2 bbb xxx-xxx-xxxx So, in this post we will see how we can select that using T-SQL on Microsoft SQL Server 2008 We can achieve this in several ways: 1) Using  Row_Number ()   Row_Number() Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.  T-SQL Script:          SELECT ROW_NUMBER() OVER (ORDER BY CUSTOMER_ID) AS [S.No]                         ,CUSTOMER_NAME                         ,CUSTOMER_PHONE_NUMBER         FROM database.dbo.tblTableName 2) Using  Identity (Property)   Identity() property creates an identity column in the table. So, first we need to cre

SQL Server Versions and Code Names :- Version Year Release Name Codename 1.0 ( OS/2 ) 1989 SQL Server 1.0 (16bit) - 1.1 ( OS/2 ) 1991 SQL Server 1.1 (16bit) - 4.21 ( WinNT ) 1993 SQL Server 4.21 SQLNT 6.0 1995 SQL Server 6.0 SQL95 6.5 1996 SQL Server 6.5 Hydra 7.0 1998 SQL Server 7.0 Sphinx - 1999 SQL Server 7.0 OLAP  Tools Plato 8.0 2000 SQL Server 2000 Shiloh 8.0 2003 SQL Server 2000 64-bit Edition Liberty 9.0 2005 SQL Server 2005 Yukon 10.0 2008 SQL Server 2008 Katmai 10.25 2010 SQL Azure DB CloudDB 10.5 2010 SQL Server 2008 R2 Kilimanjaro (aka KJ) 11.0 2012 SQL Server 2012 Denali Version with Service Packs... SQL Server 2012 11.0.2316 SQL Server 2012 CU1 12 Apr 2012 11.0.2100.6 SQL Server 2012 RTM 7 Mar 2012 SQL Server 2008 R2 10.50.2811 SQL Server 2008 R2 SP1 CU6 16 Apr 2012 10.50.2806 SQL Server 2008 R2 SP1 CU5 22 Feb 2012 10.50.2796 SQL Server 2008 R2 SP1 CU4 9 Jan 2012 10.50.2789.0 SQL Server 2008 R2 SP1 CU3 17 Oct 2011 10.50.2772.0 SQL