You probably already know that string comparison is dependent on the collation. but sometimes this dependency is slightly strange. I have installed an SQL Server with LATIN1_GENERAL_CI_AS as default collation (like is also the default setting during installation). Some weeks ago I came across a specific behavior in combination of this collation and the german language.

German is a stunning language! really. You may know that we have a tendency to Germans have some special vowels (the “Umlaut”: ä, ö, and ü) and also that extra ß which is often (but not always) simply interchangeable with “ss”. look at this query that uses the default collation for comparison:
select case
 when 'ss' collate latin1_general_ci_as
     = 'ß' collate latin1_general_ci_as
      then 'Yes'
 else 'No'
end [ss=ß?]

Considering that LATIN1_GENERAL_CI_AS is the default collation, the query above is identical to the following query:
select case
 when 'ss' = 'ß' then 'Yes'
 else 'No'
end [ss=ß?]

In both cases the result is Yes, so “ß” is considered as to be identical to “ss” when using the LATIN1_GENERAL_CI_AS collation. Now it's time to look at this query:

select case
 when 'ä' = 'ae' then 'Yes'
 else 'No'
end [ä=ae?]

This time the answer is no which is somewhat surprising, since one could consider the overall behavior inconsistent. In German, “ä” and “ae” (and also “ö” and “oe” in addition as “ü” and “ue”) are just as interchangeable as “ß” and “ss”. When this happened to me, I started browsing books online. There’s a hint, where you're advised setting the default collation to LATIN1_GENERAL_BIN for new installations. I didn’t know this so far, but ok: let’s retry our experiment:

select case
 when 'ss' collate latin1_general_bin
     = 'ß' collate latin1_general_bin
      then 'Yes'
 else 'No'
end [ss=ß?]

Now the answer is no. but considering the fact that most newer installations use LATIN1_GENERAL_CI_AS, simply because that’s the default during installation, I can’t just change the collation to LATIN1_GENERAL_BIN, since this will certainly produce other problems with queries spanning multiple databases with totally different collations. Not taking into account that changing the collation for an existing server and all of its databases/columns is a cumbersome and also very risky task on its own.

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

In this tutorial, I will show you how to Return Value in SQL Server  from EXEC Function. If you specify the OUTPUT keyword for a parameter in the procedure definition, the procedure can return the current value of the parameter to the calling program when the procedure exits. The following Stored Procedure is used which returns an Integer value 1 if the StudentId exists and 0 if the StudentId does not exists.

CREATE PROCEDURE CheckStudentId 
@StudentId INT 
AS 
BEGIN 
SET NOCOUNT ON; 
DECLARE @Exists INT 
IF EXISTS(SELECT StudentId FROM Students WHERE StudentId = @StudentId) 
BEGIN 
SET @Exists = 1 
END 
ELSE 
BEGIN 
SET @Exists = 0 
END 
RETURN @Exists 
END 

Returned value from EXEC function:
The returned integer value from the Stored Procedure, you need to make use of an Integer variable and use along with the EXEC command while executing the Stored Procedure.

    DECLARE @ReturnValue INT 
    EXEC @ReturnValue = < Store Procedure Name > < Parameters > Select @ReturnValue 
    Example: DECLARE @ReturnValue INT 
    EXEC @ReturnValue = CheckStudentId 34 
    SELECT @ReturnValue 

If There are valid StudentId then Output will be : 1

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

Today, let me show you how to empty and delete all SQL Database. Now write the following code snippet for Clear Blank SQL Database:

    DECLARE @name VARCHAR(128) 

    DECLARE @SQL VARCHAR(254) 

    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name]) 

    WHILE @name is not null 

    BEGIN 

        SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']' 

        EXEC (@SQL) 

        PRINT 'Dropped Procedure: ' + @name 

        SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name]) 

    END 

    GO 

    /* Drop all views */ 

    DECLARE @name VARCHAR(128) 

    DECLARE @SQL VARCHAR(254)

    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name]) 

    WHILE @name IS NOT NULL 

    BEGIN 

        SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']' 

        EXEC (@SQL) 

        PRINT 'Dropped View: ' + @name 

        SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name]) 

    END 

    GO 

    /* Drop all functions */ 

    DECLARE @name VARCHAR(128)  

    DECLARE @SQL VARCHAR(254)     

    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name])      

    WHILE @name IS NOT NULL 

    BEGIN 

        SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']' 

        EXEC (@SQL) 

        PRINT 'Dropped Function: ' + @name 

        SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] > @name ORDER BY [name]) 

    END 

    GO      

    /* Drop all Foreign Key constraints */ 

    DECLARE @name VARCHAR(128) 

    DECLARE @constraint VARCHAR(254) 

    DECLARE @SQL VARCHAR(254)      

    SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)      

    WHILE @name is not null 

    BEGIN 

        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME) 

        WHILE @constraint IS NOT NULL 

        BEGIN 

            SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']' 

            EXEC (@SQL) 

            PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name 

            SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME) 

        END 

    SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME) 

    END 

    GO      

    /* Drop all Primary Key constraints */ 

    DECLARE @name VARCHAR(128) 

    DECLARE @constraint VARCHAR(254) 

    DECLARE @SQL VARCHAR(254)      

    SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)      

    WHILE @name IS NOT NULL 

    BEGIN 

        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME) 

        WHILE @constraint is not null 

        BEGIN 

            SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']' 

            EXEC (@SQL) 

            PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name 

            SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME) 

        END 

    SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME) 

    END 

    GO      

    /* Drop all tables */ 

    DECLARE @name VARCHAR(128) 

    DECLARE @SQL VARCHAR(254)      

    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])      

    WHILE @name IS NOT NULL 

    BEGIN 

        SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']' 

        EXEC (@SQL) 

        PRINT 'Dropped Table: ' + @name 

        SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name]) 

    END 

    GO 

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

In this tutorial, we will share to you how to check available colum in a table. If you want to check if the column is already available in the table, you can use system views like sys.columns or INFORMATION_SCHEMA.COLUMNS.

Step 1

First, let us create the dataset:

USE TEMPDB;
CREATE TABLE TESTING(ID INT, NAME VARCHAR(100))

Step 2

Suppose you want to find out the existence of the column named NAME and print a message. You can do it by using any of the following methods:

IF EXISTS
(
SELECT * FROM SYS.COLUMNS
WHERE NAME='NAME' AND OBJECT_ID=OBJECT_ID('TESTING')
)
PRINT 'COLUMN EXISTS'
--
IF EXISTS
(
SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME='NAME' AND TABLE_NAME='TESTING'
)
PRINT 'COLUMN EXISTS'

There is also a shorter way to do this. You can use COL_LENGTH system function:

IF (SELECT COL_LENGTH('TESTING','NAME')) IS NOT NULL
PRINT 'COLUMN EXISTS'

What it does is that it finds the length of the column. If it is null, the column does not exist in the table otherwise it exists.

Simple and fast, right?

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

I have an existing table referred to as Persion. in this table I actually have five columns:
persionId
Pname
PMid
Pdescription
Pamt

When I created this table, I set PersionId and Pname because the primary key. I now wish to include an extra column within the primary key - PMID. however can i write an ALTER statement to try and do this? (I already have 1000 records in the table). First, you can drop constraint and recreate it with the following code:
alter table Persion drop CONSTRAINT <constraint_name>
alter table Persion add primary key (persionId,Pname,PMID)

You can find the constraint name by using the code below:
select OBJECT_NAME(OBJECT_ID) AS NameofConstraint
FROM sys.objects
where OBJECT_NAME(parent_object_id)='Persion'
and type_desc LIKE '%CONSTRAINT'

I think something like this should work
-- drop current primary key constraint
ALTER TABLE dbo.persion
DROP CONSTRAINT PK_persionId;
GO

-- add new auto incremented field
ALTER TABLE dbo.persion
ADD pmid BIGINT IDENTITY;
GO

-- create new primary key constraint
ALTER TABLE dbo.persion
ADD CONSTRAINT PK_persionId PRIMARY KEY NONCLUSTERED (pmid, persionId);
GO

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

Maybe you were recently doing a clean up of your website database, then you create some tables on your database but never adding any new rows to it. In this tutorial, I will tell you how to detect empty row in a table on SQL Server database.

Here's a simple query to list all empty rows in tables in your SQL Server database using a Dynamic Management View called dm_db_partition_stats. This will return page and row-count information for every partition in the current database:

;WITH EmptyRows AS
(
   SELECT SUM(row_count) AS [TotalRows],
          OBJECT_NAME(OBJECT_ID) AS TableName
   FROM sys.dm_db_partition_stats
   WHERE index_id = 0 OR index_id = 1
   GROUP BY OBJECT_ID
)
SELECT * FROM EmptyRows
WHERE [TotalRows] = 0

And here's the output:

Easy right?

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

As a part of the Disaster recovery procedures, I wished to script out each server object that we had created. This enclosed SQL Server jobs, logins, operators, coupled servers and proxies. i used to be able to script out everything but the SQL Server Agent operators:

USE msdb
set nocount on;

create table #tbl (
id int not null,
name sysname not null,
enabled tinyint not null,
email_address nvarchar(100) null,
last_email_date int not null,
last_email_time int not null,
pager_address nvarchar(100) null,
last_pager_date int not null,
last_pager_time int not null,
weekday_pager_start_time int not null,
weekday_pager_end_time int not null,
Saturday_pager_start_time int not null,
Saturday_pager_end_time int not null,
Sunday_pager_start_time int not null,
Sunday_pager_end_time int not null,
pager_days tinyint not null,
netsend_address nvarchar(100) null,
last_netsend_date int not null,
last_netsend_time int not null,
category_name sysname null);

insert into #tbl
  EXEC sp_help_operator;

select 'USE msdb;' + char(13) + char(10) + 'if exists (select * from dbo.sysoperators where name =' + quotename(name, char(39)) + ') ' + char(13) + char(10) +
'exec sp_add_operator ' +
'@name = ' + quotename(name, char(39)) + ', ' +
'@enabled = ' + cast (enabled as char(1)) + ', ' +
'@email_address = ' + quotename(email_address, char(39)) + ', ' +
case
when pager_address is not null then '@pager_address = ' + quotename(pager_address, char(39)) + ', '
else ''
end +
'@weekday_pager_start_time = ' + ltrim(str(weekday_pager_start_time)) + ', ' +
'@weekday_pager_end_time = ' + ltrim(str(weekday_pager_end_time)) + ', ' +
'@Saturday_pager_start_time = ' + ltrim(str(Saturday_pager_start_time)) + ', ' +
'@Saturday_pager_end_time = ' + ltrim(str(Saturday_pager_end_time)) + ', ' +
'@Sunday_pager_start_time = ' + ltrim(str(Sunday_pager_start_time)) + ', ' +
'@Sunday_pager_end_time = ' + ltrim(str(Sunday_pager_end_time)) + ', ' +
'@pager_days = ' + cast(pager_days as varchar(3)) +
case
when netsend_address is not null then ', @netsend_address = ' + quotename(netsend_address, char(39))
else ''
end +
case
when category_name != '[Uncategorized]' then ', @category_name = ' + category_name
else ''
end +
'; ' + char(13) + char(10) + 'go'
from #tbl order by id;

drop table #tbl;

I hope it works for you!

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.

There are three types of triggers. Basically triggers are classified in to two main type

• Insert Of Trigger.
• After Trigger.

This After Trigger run after an insert, update, or delete on table. They are not support view.
So we can say that after trigger also classified in to three types:-

• AFTER INSERT trigger.
• AFTER UPDATE trigger.
• AFTER DELETE trigger.

Insert Trigger
Whenever a row is inserted in the Customers Table, the following trigger will be executed. The newly inserted record is available in the INSERTED table. The following Trigger is fetching the CustomerId of the inserted record and the fetched value is inserted in the CustomerLogs table. Now, write the following code:
CREATE TRIGGER [dbo].[Customer_INSERT]
ON [dbo].[Customers]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CustomerId INT
SELECT @CustomerId = INSERTED.CustomerId      
FROM INSERTED
INSERT INTO CustomerLogs
VALUES(@CustomerId, 'Inserted')
END

Update Trigger
In the below code is an example of an After Update Trigger. Now write the following code:
CREATE TRIGGER [dbo].[Customer_UPDATE]
ON [dbo].[Customers]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;

DECLARE @CustomerId INT
DECLARE @Action VARCHAR(50)

SELECT @CustomerId = INSERTED.CustomerId      
FROM INSERTED

IF UPDATE(Name)
BEGIN
      SET @Action = 'Updated Name'
END

IF UPDATE(Country)
BEGIN
      SET @Action = 'Updated Country'
END

INSERT INTO CustomerLogs
VALUES(@CustomerId, @Action)
END

HostForLIFE.eu SQL Server 2014 Hosting

HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

SQL Server database files are organized in 8KB (8192 bytes) chunks, called pages. When we create the first row in a table, SQL Server allocates an 8KB page to store that row. Similarly every row in every table ends up being stored in a page.

Say one of the pages in your table is corrupt and while repairing the corrupt pages, you may eventually end up loosing some data. You may want to find out which records are on the page. To do so, use the following undocumented T-SQL %%physloc%% virtual column:

USE AdventureWorks2014
GO
SELECT *, %%physloc%% AS physloc
FROM Person.AddressType
ORDER BY physloc;

As you can see, the last column represents the record location. However the hexadecimal value is not in a human readable format. To read the physical record of each row in a human readable format, use the following query:

SELECT *
FROM Person.AddressType
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)

The sys.fun_PhysLocCracker function takes the %%physloc%% and represents a human readable format fileid, pageid i.e. 880 and record number on the page 880.

If you are interested in knowing what’s inside the sys.fn_PhysLocCracker function, use sp_helptext as follows:

EXEC sp_helptext 'sys.fn_PhysLocCracker'
which display the definition of sys.fn_PhysLocCracker
-------------------------------------------------------------------------------
-- Name: sys.fn_PhysLocCracker
--
-- Description:
--    Cracks the output of %%physloc%% virtual column
--
-- Notes:
-------------------------------------------------------------------------------
create function sys.fn_PhysLocCracker (@physical_locator binary (8))
returns @dumploc_table table
(
    [file_id]    int not null,
    [page_id]    int not null,
    [slot_id]    int not null
)
as
begin
    declare @page_id    binary (4)
    declare @file_id    binary (2)
    declare @slot_id    binary (2)
    -- Page ID is the first four bytes, then 2 bytes of page ID, then 2 bytes of slot
    --
    select @page_id = convert (binary (4), reverse (substring (@physical_locator, 1, 4)))
    select @file_id = convert (binary (2), reverse (substring (@physical_locator, 5, 2)))
    select @slot_id = convert (binary (2), reverse (substring (@physical_locator, 7, 2)))
   
    insert into @dumploc_table values (@file_id, @page_id, @slot_id)
    return
end
The undocumented sys.fn_PhysLocCracker works on SQL Server 2008 and above.

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

Sometimes we'd like to understand how much space utilized by databases in SQL Server. There are multiple ways that to know the database size in SQL SERVER.
1. using Table Sys.master_files
2. using stored Proc sp_spaceused
3. using Manual option in SSMS

Using Table Sys.master_files
This is one option by that we will know database size. Below query uses 2 tables databases that contains database ID, Name etc and another table master_files which contains size columns holds size of database. By using Inner join(database ID) we are getting database size. each tables are present in master database.
SELECT     sys.databases.name, 
           CONVERT(VARCHAR,SUM(size)*8/1024)+' MB' AS [Total disk space] 
FROM       sys.databases  
JOIN       sys.master_files 
ON         sys.databases.database_id=sys.master_files.database_id 
GROUP BY   sys.databases.name 
ORDER BY   sys.databases.name

See the following picture after executing above code which gives all the databases with their sizes.

Using stored Proc sp_spaceused 

This  is second choice to recognize database size. Here we are going to call stored procedure sp_spaceused which is present in master database. This one helps to know size of current database.
exec sp_spaceused  

After calling above stored procedure it shows  below Image2 which contains column called database_size surrounded by red mark.

Using Manual Option in SSMS
This is another option to know database size. To know size Go to Server Explorer -> Expand it -> Right click on Database -> Choose Properties -> In popup window choose General tab ->See Size property which is marked(red) in Image3.

Image 3: Manual option to get Database size
Hope it helps you to get database size in SQL Server!

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.