IntelliSense is not working even though it is enabled.

I am using SSMS 2016,  My IntelliSense was working fine till few days back and suddenly one day it stopped working.

 I ignored it as I thought it will get fixed automatically.

But it was not fixed automatically, I have tried following options to get it fixed.

First tried with cache refresh.

     1.  Hit CTRL + Shift + R

     2.  Edit - > IntelliSence - > Refresh Local Cache

     and tried below options as well.

        

     1.  Go to Tools -> Options ->Text Editor -> General ->  Selected both the options  Auto list             members and Parameters information

      2. Go to Tools -> Options ->Text Editor -> IntelliSence -> Select option Enable IntelliSense. (Make sure it is enabled)

     3. Please make sure SQL CMD mode should be disabled for IntelliSense.

Tools -> Options -> Query Execution -> SQL Server - > General

Option ‘By default, Open new query in SQL CMD mode’ is disabled.

     Mostly the above trials will solve the problem, but unfortunately in my case, it didn’t worked so I have tried the below way.

     Go to Tools -> Options ->Text Editor -> IntelliSense -> Maximum script size setting Changed to unlimited.

    If this is also not working for you, it seems like an issue with installation. I recommend to re-install the tools.

    Thank you for visiting the blog, hope you got the required information.

How to restore database from backup file.

In this article, we will be discussing how to restore the database from the backup file.


If you are restoring the backup file for the first time you can restore using the below command.

RESTORE DATABASE AdventureWorks2012 FROM DISK='C:\Ganesh\Backup\AdventureWorks.bak' WITH REPLACE

Now, Let's see other scenario where the same backup file to be restored into another database ( New database is created in the same location of the previous database)

Let's try whether the above restore command works or not.

Here I am trying to restore the same backup file to another database which is AdventureWorks2012_NEW DB.

Command :

RESTORE DATABASE AdventureWorks2012_NEW FROM DISK='C:\Ganesh\Backup\AdventureWorks.bak' WITH REPLACE

Error:

Msg 1834, Level 16, State 1, Line 8

The file 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQL2012RTM\MSSQL\DATA\AdventureWorks2012.mdf' cannot be overwritten.  It is being used by database 'AdventureWorks2012'.

Msg 3156, Level 16, State 4, Line 8

File 'AdventureWorks2012' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQL2012RTM\MSSQL\DATA\AdventureWorks2012.mdf'. Use WITH MOVE to identify a valid location for the file.

Msg 1834, Level 16, State 1, Line 8

The file 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQL2012RTM\MSSQL\DATA\AdventureWorks2012_log.ldf' cannot be overwritten.  It is being used by database 'AdventureWorks2012'.

Msg 3156, Level 16, State 4, Line 8

File 'AdventureWorks2012_log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQL2012RTM\MSSQL\DATA\AdventureWorks2012_log.ldf'. Use WITH MOVE to identify a valid location for

the file.

Msg 3119, Level 16, State 1, Line 8

Problems were identified while planning for the RESTORE statement. Previous messages provide details.

Msg 3013, Level 16, State 1, Line 8

RESTORE DATABASE is terminating abnormally.

As the physical files already exist in that location, the backup command is unable to restore it. for this, we have to use the backup command with the move clause.

Picking logical names from backup using the below command.

RESTORE FILELISTONLY FROM DISK = 'C:\Ganesh\Backup\AdventureWorks.bak'

Restore command :

RESTORE DATABASE [AdventureWorks2012_NEW] FROM  DISK = N'C:\Ganesh\Backup\AdventureWorks.bak' WITH 

FILE = 1,  MOVE N'AdventureWorks2012' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQL2012RTM\MSSQL\DATA\AdventureWorks2012_NEW.mdf',

 MOVE N'AdventureWorks2012_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQL2012RTM\MSSQL\DATA\AdventureWorks2012_NEW_log.ldf',

NOUNLOAD,  REPLACE,  STATS = 5

You can see in the above image. Restore is complete.


Thank you for visiting the blog. hope you got the required information.

If you like this article. Please share your feedback in the comment section.

How to update column value with column value of other table using Join condition.

In this article, we will be discussing how to update column value with the column value of other table.

This is a very common requirement. Most often we will come across this situation.

Let's Create a table and insert sample data:

Here we are creating two tables.

CREATE TABLE UPDATETEST1 ( ID INT, VALUE NVARCHAR(250) ) 

INSERT INTO UPDATETEST1 VALUES (1,'ABC') 

INSERT INTO UPDATETEST1 VALUES (2,'BCD') 

INSERT INTO UPDATETEST1 VALUES (3,'CDE') 

INSERT INTO UPDATETEST1 VALUES (4,'DEF') 

CREATE TABLE UPDATETEST2 (ID INT, VALUE NVARCHAR(250)) 

INSERT INTO UPDATETEST2 VALUES (1,'ABC') 

INSERT INTO UPDATETEST2 VALUES (2,'XYZ') 

INSERT INTO UPDATETEST2 VALUES (3,'CDE') 

INSERT INTO UPDATETEST2 VALUES (4,'DEF') 

Select table1:

SELECT * FROM  UPDATETEST1 

Select table2:

SELECT * FROM UPDATETEST2

In the above two tables  ID  2 having two different values in the tables.

Now, let's see how to update the table UPDATETEST2 VALUE column with table  UPDATETEST1 VALUE  column based on the ID column condition. 

Query 1: Using NON-ANSI Join 

UPDATE A 
SET    A.VALUE = B.VALUE 
FROM   UPDATETEST2 A, 
       UPDATETEST1 B 
WHERE  A.ID = B.ID 
       AND A.VALUE <> B.VALUE 

Query 2: Using ANSI Join ( American National Standards Institute) 

UPDATE A 
SET    A.VALUE = B.VALUE 
FROM   UPDATETEST2 A 
       INNER JOIN UPDATETEST1 B 
               ON A.ID = B.ID 
WHERE  A.VALUE <> B.VALUE 

Output post update:

Thank you for visiting the blog.  hope you got the required information.

String concatenation using FOR XML PATH() and STUFF

In this article, we will be discussing how to convert row data to column data with ',' separation.

We can fulfill this requirement with the help of  STUFF , Substring and FOR XML PATH() very easily.



Let's create a table with sample data.


CREATE TABLE ForXmlExp (ID INT, NAME VARCHAR(250)) 


INSERT INTO ForXmlExp VALUES (1,'Ganesh') 

INSERT INTO ForXmlExp VALUES (2,'Mohan') 

INSERT INTO ForXmlExp VALUES (3,'Raju') 

INSERT INTO ForXmlExp VALUES (4,'Kiran') 

INSERT INTO ForXmlExp VALUES (5,'Charan') 

INSERT INTO ForXmlExp VALUES (1,'Rajesh') 

INSERT INTO ForXmlExp VALUES (2,'Venkat') 

select table:

Select * from  ForXmlExp 

FOR XML PATH( )  clause returns the SQL results as XML elements.
By giving empty (' ') string in PATH argument and concatenated  ','  with Name column. we will get below results.

Query:

SELECT ',' + NAME FROM   ForXmlExp FOR xml path('')

OutPut:

,Ganesh,Mohan,Raju,Kiran,Charan,Rajesh,Venkat


In the above output we can see Row data converted to the column data, but one additional ',' is there in the first position. That we can remove using STUFF , Substring functions.


Query to remove leading comma.


SELECT Stuff((SELECT ',' + NAME 
              FROM   ForXmlExp 
              FOR XML PATH('')),1,1,'') 

OR

we can write the same logic with Substring As well.


SELECT Substring((SELECT ',' + NAME 
                  FROM   ForXmlExp 
                  FOR XML PATH('')), 1, 8000) 



Output:

 Ganesh,Mohan,Raju,Kiran,Charan,Rajesh,Venkat


As of now, we worked on a particular column, Now let's work on the table and get the result based on the ID column and applying  Group By on ID Column.


Query:

SELECT ID, 
       Stuff((SELECT ',' + NAME 
              FROM   ForXmlExp T1 
              WHERE  T1.ID = T2.ID 
              FOR XML PATH ('')), 1, 1, '') AS Result 
FROM   ForXmlExp T2 
GROUP  BY ID 


Output:

Thank you for visiting the blog, hope you got the required information.

How to identify the duplicate records and delete those records from the table.

In this article, we will be discussing how to delete duplicate records from the table. Here we are using
ROW_NUMBER()  function.

ROW_NUMBER() OVER(PARTITION BY [] ORDER BY [] )

Creating Table :

CREATE TABLE DELETEDUPLICATERECORD  (ID INT,NAME VARCHAR(250),MAIL VARCHAR(250), 
     ADDRESS VARCHAR(250),PINDODE INT ) 


Inserting Sample Data:

INSERT INTO DELETEDUPLICATERECORD 
VALUES (1,'Ganesh','Ganesh@gmail.com', 'Hyd','500000') 

INSERT INTO DELETEDUPLICATERECORD 
VALUES (2,'Mohan','Mohan@gmail.com','Hyd','500000') 

INSERT INTO DELETEDUPLICATERECORD 
VALUES  (3, 'Raju','Raju@gmail.com','Hyd', '500000') 

INSERT INTO DELETEDUPLICATERECORD 
VALUES  (4,'Kiran','Kiran@gmail.com','Hyd','500000') 

INSERT INTO DELETEDUPLICATERECORD 
VALUES  (5,'Charan','Charan@gmail.com','Hyd','500000') 

INSERT INTO DELETEDUPLICATERECORD 
VALUES  (6,'Ganesh','Ganesh@gmail.com','Hyd','500000') 

INSERT INTO DELETEDUPLICATERECORD 
VALUES  (7,'Mohan','Mohan@gmail.com','Hyd','500000') 

Select table:

SELECT * FROM   DELETEDUPLICATERECORD 










in the above image, we can see the table 'DELETEDUPLICATERECORD' having two duplicate records.

Now, let us identify those two records and delete them.

Query to identify duplicate  records:

SELECT *, 
       ROW_NUMBER() 
         OVER ( 
           partition BY NAME, MAIL 
           ORDER BY ID ) DuplicateCount 
FROM   DELETEDUPLICATERECORD 

OutPut:

In the above image, DuplicateCount column tells us how many duplicate records are there


Now filtering only duplicate records.

SELECT * 
FROM   (SELECT *, 
               ROW_NUMBER() 
                 OVER ( 
                   partition BY NAME, MAIL 
                   ORDER BY ID ) DuplicateCount 
        FROM   DELETEDUPLICATERECORD) T 
WHERE  DuplicateCount >= 2 


OutPut:

Deleting the records.


DELETE T 
FROM   (SELECT *, 
               ROW_NUMBER() 
                 OVER ( 
                   partition BY NAME, MAIL 
                   ORDER BY ID ) DuplicateCount 
        FROM   DELETEDUPLICATERECORD) T 
WHERE  DuplicateCount >= 2 

Final Results after deleting duplicate records.


SELECT * FROM   DELETEDUPLICATERECORD 

Thank you for visiting the Blog, hope you got the required information.