SQL Server 2012 – Ctrl+R Show/Hide Results Pane missing

To use Ctrl+R in SQL Server 2012 use below steps

1) Open SSMS.
2) Go to Tools -> Options.
3) In the left panel click on “Keyboard” for selecting it.
4) Once “Keyboard” is selected in top of the right panel press the “Reset” button.
5) Answer “yes” to the question dialog “Keyboard mappings will reset to their defaults. Continue ?”
6) Click OK in the Options dialog.

After following above steps you are able to use Ctrl+R and other SQL Server Short Cut methods which was working as per older version or expectation.

SQL Server Table Recreation Issue

While working with SQL Server we may get below error :

Saving changes is not permitted. The changes you have made require that the following tables to dropped and recreate.
You have either made changes to the table that can’t be recreate or enable the option prevent saving changes that require the tables to be recreate.

We can disable above error message by SQL Server Environment settings

Follow below steps to configure SQL Server environment

Log in to SSMS

Navigate to

Tools – Options – Designers

Select Check box – Prevent saving changes that require the table re-creation

This option will help you in above error.

How to test remote Database Connection String

Simple way to check connectivity of SQL Server

Create a file with extension UDL (Universal Data Link ).

e.g. Create Text Document and rename its extension with “.UDL”

Once UDL file is available you can easily check database connection string.

Sql Server Remove Duplicate Value From a Table

How to Remove Duplicate Value From a Table

If We have a table like following and we need to remove Duplicate Value

ID FNAME LNAME
1 AAA CCC
2 BBB DDD
1 AAA CCC
2 BBB DDD
1 AAA CCC
2 BBB DDD
3 BCB DGD

If we want to remove duplicate value from above table
Than we can use following Query

SELECT DISTINCT * INTO NEWTABLE FROM OLDTABLE
TRUNCATE TABLE OLDTABLE
INSERT INTO OLDTABLE SELECT * FROM NEWTABLE

Sql Server Remove Duplicate Value From a Table

Sql Server Count Duplicate Records – Rows

Count Duplicate Records – Rows

If We have a table like following and we need to find No of Duplicate column than follow 

1	AAA	CCC
2	BBB	CCC
2	BBB	DDD
3	CCC	DDD
3	CCC	DDD
3	CCC	DDD
1	AAA	CCC
1	AAA	CCC
1	AAA	CCC
1	AAA	CCC
2	BBB	DDD
2	BBB	DDD


If You want to Count Duplicate Values in Column 

SELECT ID , COUNT (*) AS COUNTMULIPLE FROM SAMPLE 
GROUP BY ID 
HAVING COUNT(*) > 1

 

TO See Directory Inforamation From Sql Server

TO See Directory Inforamation From Sql Server

Use Following Query
–USE master
–GO
–EXEC sp_configure ‘show advanced options’, 1
–GO
–RECONFIGURE WITH OVERRIDE
–GO
–EXEC sp_configure ‘xp_cmdshell’, 1
–GO
–RECONFIGURE WITH OVERRIDE
–GO
–EXEC sp_configure ‘show advanced options’, 0
–GO

EXEC master ..xp_cmdshell ‘dir c:\’

Asp.Net Sql Server Case Condition with Date Null Value

Asp.Net Date Field Case Condition When it Contain Null Value

SELECT

DateColumn,

(CASE  DateColumn WHEN NULLIF(DateColumn, NULL)  THEN ‘Completed’ else ‘Pending’ end ) as CompletedStatus

FROM

TableNAme

Sql Server Some Useful Query

This Query is Used to Create a new table from Existing Table
Select top 0 * into Demo from MaterialMaster

Sql Server Select Into Syntax
SELECT Id, Name INTO Employee_Temp FROM Employee WHERE Id > 1

Insert  Into Temporary Table
SELECT * INTO #MyContacts FROM Employee

How to Find Duplicate Records In Table

SELECT ColumnName,
COUNT(ColumnName) AS ColumnName
FROM tbl_Employee_M
GROUP BY ColumnName
HAVING ( COUNT(ColumnName) > 1 )

Sql Server Indexes

The concept behind indexes is to change the order of the data (clustered index)
or to add metadata (non-clustered index) for improving the performance of queries.

SQL Server use indexes to find data quickly when a query is processed.

Clustered indexes

Clustered indexes define the physical sorting of a database table’s rows in the storage media.
For this reason, each database table may have only one clustered index.
If a PRIMARY KEY constraint is created for a database table and no clustered index currently exists for that table,
SQL Server automatically creates a clustered index on the primary key.

* Physically stored in order (ascending or descending)
* Only one per table
* When a primary key is created a clustered index is automatically created as well.
* If the table is under heavy data modifications or the primary key is used for searches, a clustered index on the primary key is recommended.
* Columns with values that will not change at all or very seldom, are the best choices.

Non-clustered indexes

* Up to 249 nonclustered indexes are possible for each table or indexed view.
* The clustered index keys are used for searching therefore clustered index keys should be chosen with a minimal length.
* Covered queries (all the columns used for joining, sorting or filtering are indexed) should be non-clustered.
* Foreign keys should be non-clustered.
If the table is under heavy data retrieval from fields other than the primary key, one clustered index and/or one or more non-clustered indexes should be created for the column(s) used to retrieve the data.

Sql Server Pass Comma Seperated Parameter in Procedure

How to Pass Multi-Value Parameters to SQL Server Stored Procedure

I got This Article From http://www.dotnetspider.com/

To use  Comma Seperated parameter value like 1,2,3  Make Following Function in Sql Server

ALTER FUNCTION [dbo].[fn_Split_Up_Ids]
(
@Param_Ids varchar(500)
)
RETURNS @Id_Table TABLE(IDField int)

AS
BEGIN
IF (LEN(@Param_Ids) <= 0)
RETURN

DECLARE @CommaPos smallint
SET @CommaPos = CHARINDEX(‘,’, RTRIM(LTRIM(@Param_Ids)))

IF @CommaPos = 0
INSERT INTO @Id_Table
VALUES(CONVERT(BIGINT ,RTRIM(LTRIM(@Param_Ids))))
ELSE
BEGIN
WHILE LEN(@Param_Ids) > 1
BEGIN
SET @CommaPos = CHARINDEX(‘,’, RTRIM(LTRIM(@Param_Ids)))
INSERT INTO @Id_Table
VALUES(CONVERT(INT ,SUBSTRING(RTRIM(LTRIM(@Param_Ids)),1, @CommaPos – 1)))
SET @Param_Ids = SUBSTRING(RTRIM(LTRIM(@Param_Ids)), @CommaPos + 1 , LEN(RTRIM(LTRIM(@Param_Ids))))
SET @CommaPos = CHARINDEX(‘,’, RTRIM(LTRIM(@Param_Ids)))
IF @CommaPos = 0
BEGIN
INSERT INTO @Id_Table VALUES(CONVERT(INT ,RTRIM(LTRIM(@Param_Ids))))
BREAK
END
END
END
RETURN
END

And From Above Function

To use the id in the IN clause of the SELECT statement

SELECT * FROM COUNTRY
WHERE Country_Id IN (SELECT * FROM dbo.fn_Split_Up_Ids(‘9,4,2,6,10’))

if you use this values from parameter than

SELECT * FROM COUNTRY
WHERE Country_Id IN (@Country_List)

But this will not work if you write this query in a stored procedure. Therefore you need to use the above function to pass the multi-value paramters to a stored procedure.

The Above Function returns data in integer so for parameter which are in   varchar use below function
This Function will return value in Varchar
Alter FUNCTION [dbo].[fn_Split_Up_Ids]
(
@Param_Ids varchar(500)
)
RETURNS @Id_Table TABLE(IDField varchar(1000))

AS
BEGIN
IF (LEN(@Param_Ids) <= 0)
RETURN

DECLARE @CommaPos smallint
SET @CommaPos = CHARINDEX(‘,’, RTRIM(LTRIM(@Param_Ids)))

IF @CommaPos = 0
INSERT INTO @Id_Table
VALUES (RTRIM(LTRIM(@Param_Ids)))
ELSE
BEGIN
WHILE LEN(@Param_Ids) > 1
BEGIN
SET @CommaPos = CHARINDEX(‘,’, RTRIM(LTRIM(@Param_Ids)))
INSERT INTO @Id_Table
VALUES(SUBSTRING(RTRIM(LTRIM(@Param_Ids)),1, @CommaPos – 1))
SET @Param_Ids = SUBSTRING(RTRIM(LTRIM(@Param_Ids)), @CommaPos + 1 , LEN(RTRIM(LTRIM(@Param_Ids))))
SET @CommaPos = CHARINDEX(‘,’, RTRIM(LTRIM(@Param_Ids)))
IF @CommaPos = 0
BEGIN
–INSERT INTO @Id_Table VALUES(CONVERT(INT ,RTRIM(LTRIM(@Param_Ids))))
INSERT INTO @Id_Table VALUES(RTRIM(LTRIM(@Param_Ids)))
BREAK
END
END
END
RETURN
END