How can I Truncate/Clear SQL Server Error Log ?

We may have faced an issue like SQL Server error log is getting filled with entries...!

So, We just want to clear the error log entries.. right ?

Oh.. Wait!

First, Tell me that How to read the entries from my SQL Server Error Log ?  (from Active/current file) then we can move into further ...:)

Read SQL Server Error log (Active file)
EXEC master.dbo.xp_readerrorlog 0, 1, NULL, NULL, NULL, NULL, N'desc'

Ok. Then how to write a new entry into the SQL server Error log ?

Log an entry into SQL Server Error log
RAISERROR('SQL Server Buddy',16,1) WITH LOG

Now,  Can we go further to clear the log entry ? YES

we can use either...

To clear SQL Server error log
DBCC errorlog
--or
EXEC sp_cycle_errorlog

NULL = NULL

One of my friend had discussion with me some days back that NULL is not equal to another NULL...! 

Is that TRUE ?

I said that "NO, IT IS NOT ALWAYS...!"

What that means.. "NOT ALWAYS" ?

In SQL Server, we have SET option called "ANSI_NULLS"

Is there any way to identify that what are all the options have been set to ON ?

DBCC USEROPTIONS
GO


Its a session specific option. So, It will list out What are all the user options have been set to ON along with some other options!

OK.

NULL is NOT EQUAL to another NULL. How ? 


When ANSI_NULLS is set to ON

SET ANSI_NULLS ON
GO


IF (NULL=NULL)
    PRINT 'EQUAL'
ELSE
    PRINT
'NOT EQUAL'
GO


Result : NOT EQUAL

 

NULL is EQUAL to another NULL. How ? 

When ANSI_NULLS is set to OFF

SET ANSI_NULLS OFF
GO

IF
(NULL=NULL)
    PRINT 'EQUAL'
ELSE
    PRINT
'NOT EQUAL'
GO

Result : EQUAL


So, NULL is not equal to another NULL - NOT ALWAYS, It depends on ANSI_NULLS option!

Unique Vs. Primary Key - Referential Integrity - Have you tried...?

When we create a Referential integrity, Parent column can be a PRIMARY KEY or UNIQUE column!
 

OK.
1. Crete a Parent Table
CREATE TABLE MasterTable1
(
Id    INT IDENTITY(1,1) PRIMARY KEY,
Column1 VARCHAR(10)
)
GO

2. Create a Child table & Refer MasterTable1
CREATE TABLE ChildTable1
(
Id    INT IDENTITY(1,1) PRIMARY KEY,
MasterID INT FOREIGN KEY REFERENCES MasterTable1,
Column2 VARCHAR(10)
)
GO

So, The above statement uses only the Parent Table name. But, not Primary Key column.

By default, It refers the PRIMARY KEY column of the reference table(MasterTable1). So, no need to give the PRIMARY KEY column name explicitly.

But, Have you tried with UNIQUE key for the same scenario ?


1. Crete a Parent Table
CREATE TABLE MasterTable1
(
Id    INT IDENTITY(1,1) UNIQUE,
Column1 VARCHAR(10)
)
GO


2. Create a Child table & Refer MasterTable1
CREATE TABLE ChildTable1
(
Id    INT IDENTITY(1,1) PRIMARY KEY,
MasterID INT FOREIGN KEY REFERENCES MasterTable1,
Column2 VARCHAR(10)
)
GO

You will get an Err message!

Msg 1773, Level 16, State 0, Line 1
Foreign key 'FK__ChildTabl__Maste__658C0CBD' has implicit reference to object 'MasterTable1' which does not have a primary key defined on it.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.


So, when referring an UNIQUE column, It should be TableName(ColumnName)


CREATE TABLE ChildTable1
(
Id    INT IDENTITY(1,1) PRIMARY KEY,
MasterID INT FOREIGN KEY REFERENCES MasterTable1(Id),
Column2 VARCHAR(10)
)
GO


So, Using TableName(columnName) is mandatory when referring an UNIQUE key column!!!
Simple way to identify the SQL Server service is running or not!

CREATE TABLE #Services(ServerName VARCHAR(255))

INSERT  #Services
EXEC  xp_cmdshell 'NET START'

IF  EXISTS (SELECT 1 FROM #Services WHERE ServerName LIKE '%SQL SERVER%')
   SELECT 'SQL Service is running' [Status]
ELSE
   SELECT 'SQL Service is not running' [Status]


DROP  TABLE #Services
GO