SQL Server - How to create/make Foreign Key on the table object.

     Most of the time many new SQL developer do not able to create the primary key and Foreign Key relationship. In this post I am going to show this steps and it very simple to understand.

We have to create to table first for Primary Key and second for Foreign Key.

--Step1- First table creating here. With Primary Key on RuleID column.

create TABLE dbo.Table1_Rule

(

RuleID int primary key,

Rulename varchar(50),

Status bit ,

Createdate datetime,

Castupdate datetime

)

-    Step2 - Now we are creating second table with  getting reference for Foreign Key.

create  TABLE dbo.Table2_Condition

(

RuleConditionID int ,

RuleID int references dbo.Table1_Rule(RuleID ),

AppliedContent bit ,

Operation varchar(50),

value varchar(50)

)

--Step 3 - Then we are appling Foreign Key in second(dbo.Table2_Condition) table on RuleID column.

ALTER TABLE dbo.Table2_Condition

ADD FOREIGN KEY (RuleID) REFERENCES dbo.Table1_Rule(RuleID);

SQL Server – How to get all columns name list with Database Name, Table Name, Schema and other details

Run the below script and see the result.

SELECT [TABLE_CATALOG]

      ,[TABLE_SCHEMA]

      ,[TABLE_NAME]

      ,[COLUMN_NAME]

      ,[ORDINAL_POSITION]

      ,[COLUMN_DEFAULT]

      ,[IS_NULLABLE]

      ,[DATA_TYPE]

      ,[CHARACTER_MAXIMUM_LENGTH]

      ,[CHARACTER_OCTET_LENGTH]

      ,[NUMERIC_PRECISION]

      ,[NUMERIC_PRECISION_RADIX]

      ,[NUMERIC_SCALE]

      ,[DATETIME_PRECISION]

      ,[CHARACTER_SET_CATALOG]

      ,[CHARACTER_SET_SCHEMA]

      ,[CHARACTER_SET_NAME]

      ,[COLLATION_CATALOG]

      ,[COLLATION_SCHEMA]

      ,[COLLATION_NAME]

      ,[DOMAIN_CATALOG]

      ,[DOMAIN_SCHEMA]

      ,[DOMAIN_NAME]

  FROM [INFORMATION_SCHEMA].[COLUMNS]

There are many other way to get the same result in SQL Server, if you can find than please share with me. 

SQL Server – Delete the duplicate record (data) form table

 It is very easy to delete duplicate record from table in sql server. SQL Server always stores each tupple (Row) as unique into the table.

To see duplicate record, we can use the count function with group by clause with having in the condition.

 To delete the record to Max function with NOT IN keyword.

Just execute and see how it work to delete duplicate record  into table.

USE tempdb

GO

CREATE TABLE Jainendra_TestTable (My_ID INT, Rank_Col VARCHAR(50))

Go

INSERT INTO Jainendra_TestTable (My_ID, Rank_Col)

SELECT 1, 'First'

UNION ALL

SELECT 2, 'No Rank'

UNION ALL

SELECT 3, 'Second'

UNION ALL

SELECT 4, 'Second'

UNION ALL

SELECT 5, 'Second'

UNION ALL

SELECT 6, 'Third'

UNION ALL

SELECT 7, 'Five'

UNION ALL

SELECT 8, 'Second'

UNION ALL

SELECT 9, 'Five'

UNION ALL

SELECT 10, 'Nine'

UNION ALL

SELECT 11, 'Third'

GO

-- See the inserted data in create table

SELECT *

FROM Jainendra_TestTable

GO

-- Now below query is detecting duplicate records into table

SELECT Rank_Col, COUNT(*) TotalCount

FROM Jainendra_TestTable GROUP BY Rank_Col

HAVING COUNT(*) > 1 ORDER BY COUNT(*) DESC

GO

-- Now below query is deleting the duplicate record into table

DELETE FROM Jainendra_TestTable

WHERE My_ID NOT IN

( SELECT MAX(My_ID) FROM Jainendra_TestTable GROUP BY Rank_Col)

GO

-- Selecting Data

SELECT *

FROM Jainendra_TestTable

GO

DROP TABLE Jainendra_TestTable

GO

If it is useful than please like and share it to other SQL Server learners

SQL SERVER - How to create Full Text Search in SQL Server

Just follow this simple steps for Full Text Search in SQL Server
 

1.      Open the Solution Explorer in SSMS and navigate to database

2.      Into the database navigate to Storage Folder

3.      Rigth click on the full text Catalog and provide the Catalog name and press OK button

4.      Now open to database table, and right click on the table and go into the   Full Text Index option and select the ‘Define Full Text Index’ option.

5.      Then Full Text search wizard will open and click on next and choose the Indexed column or Identity column (i.e. EmployeeID) from drop down list, and when will select query will fire this indexed column must be in a query (See Example – EmployeeID).

6.      Next, then select the Column name (i.e. Title) for full text searching and select the language. Then click Next

7.      select any one the change track that are automatic(for default), manual and Do not change track, then Click Next

8.      Select the Catalog name and next and Finish.

9.      Then run the below query on SSMS.

Examples :-

   -- This query will search every Column of the table

SELECT EmployeeID , [Title]   FROM [Employee]

         where freetext (*, 'Accountant or Manager')

   -- or it will search only Title Column

SELECT EmployeeID , [Title]   FROM [Employee]

where  contains (Title , 'Accountant AND Marketing')

SQL SERVER - What is RAISERROR with details and Example

Using RAISERROR, we can throw our own error message while running our Query or Stored procedure.

·        It allows developers to generate their own messages

·        It returns the same message format that is generated by SQL Server Database Engine

·        We can set our own level of Severity for messages

·        It can be associated with Query and stored procedure

·        ERROR message can have 2047 character and show only 2044

-- syntax 

RAISERROR ( { Message ID | Message Text} { ,severity ,state }

    [ ,argument [ ,...n ] ] )

    [ WITH option [ ,...n ] ]

·        Custom error message ID should be greater than 5000.

·        Severity option should between 0 to 25 and for fatal error 20 to 25

·        State option is default set 1 but we can set 1 to 127

·        With option can set for log it can true or false like as:-

 Example:

exec sp_addmessage @msgnum=50010,@severity=1,@msgtext='my custom error message text',@with_log='true'

We can see error massage in sys.messages view and using sp_addmessage procedure we can add new custom error message in sys.messages view.

exec sp_addmessage @msgnum=50009,@severity=1,@msgtext='Adding Custom Error Message'

OR

 

            SELECT * FROM sys.messages

SQL SERVER - What is user defined data type, and how to create

 It is very esay dont worry about how to use and create it below example just run only. 

It allows defining its own T- SQL User Defined Data Type and you can use this UDDT to entire Database.

See the following example-

-- TO CREATE USER DEFINED DATA TYPE WITH VARCHAR DATA TYPE

EXEC SP_ADDTYPE TYPE1, 'VARCHAR(50)','NULL'

-- TO CREATE USER DEFINED DATA TYPE WITH INT DATA TYPE 

--Example 2 (run this Full Script)

EXEC SP_ADDTYPE  @TYPENAME =TYPE2,

@PHYSTYPE= INT,

@NULLTYPE=NULL,

@OWNER= DBO

-- Declare the variable and data type is 'TYPE2'

DECLARE @MYVARIALBE TYPE2

-- Set the value in vairaible

SET @MYVARIALBE = 100

-- Get the value from varaible

SELECT @MYVARIALBE AS MYVALUE

-- Drop user defined data type

Drop  type  TYPE2

SQL SERVER - What is transaction and What type of block of code you will use control transaction error and T-SQL Error

Before read this article i would like to say it best  to know for any any sql server user specially for sql server dba and developer.

This is very important is learn how to working transaction and how to use try and catch block in sql server within the transaction and how to get the run time error.

1. What is Transaction?

A transaction is a batch of statements that are treated as a single event and Any changes made to the database by a transaction are guaranteed either to go to completion or to have no effect at all.

2. What type of block of code you will use control transaction error and T-SQL Error?

To control transaction errors I will prefer use of begin tran commit tran, and rollback tran method and to control the T-SQL error I will use try and catch method.

Example:-(Must run this example)

USE AdventureWorks;

GO

BEGIN TRANSACTION; -- opening new transaction here

BEGIN TRY  -- try stmt started
 -- we are here writing error code as Generate a constraint violation error.

    DELETE FROM Production.Product

    WHERE ProductID = 677;

END TRY

BEGIN CATCH -- catch stmt strated

    SELECT

         ERROR_NUMBER() AS ErrorNumber

        ,ERROR_SEVERITY() AS ErrorSeverity       

        ,ERROR_LINE() AS ErrorLine
        ,ERROR_STATE() AS ErrorState

  ,ERROR_PROCEDURE() AS ErrorProcedure

        ,ERROR_MESSAGE() AS ErrorMessage;

    IF @@TRANCOUNT > 0

        ROLLBACK TRANSACTION;

END CATCH;

IF @@TRANCOUNT > 0

    COMMIT TRANSACTION;

GO

OutPut:-