Feeds:
Posts
Comments

Archive for the ‘SQL SERVER’ Category

In my earlier two articles, I demonstrated how to enable and utilize the Change Data Capture (CDC) in any SQL Server database with few simple steps. However if you enable this feature for testing purpose in the test server, or sometimes you enable it on the wrong database / table  by mistake, so you need to disable it by following few steps as shown below. Before proceeding with the disability of CDC feature in the database, you must make sure that you DO NOT need the changes recorded by CDC because once you disable it, the CDC data will no longer be available.

Let me explain how to disable the CDC from any respective table & database in few easy steps.

Step 1 – Validating SQL Server Agent:
First of all you must make sure that your SQL Server Agent is UP and RUNNING as shown below.

Change data capture.3.1_part3

Step 2 – Find capture instance name :
The next step is to find the name of the capture instance (audit table) of any particular table that you need to disable for CDC. In order to achieve it, you need to execute sys.sp_cdc_help_change_data_capture (system stored procedure) to get all the list of CDC enabled objects along with its capture instance name as shown below.

Please note, if you need to disable CDC for database ONLY kindly skip this step.

--This script is compatible with SQL Server 2008 and above.
USE AdventureWorks2012
GO
sys.sp_cdc_help_change_data_capture
GO
--OUTPUT

Change data capture.3.2_part3

Step 3 – Disable CDC for tables:
Once you have the list, you need to note the capture instance name and execute the given below script with schema, table & capture instance name.
This script will disable the CDC feature for the particular table and you will lose all the CDC data for the given below table.

Please note, if you need to disable CDC for database ONLY kindly skip this step.

--This script is compatible with SQL Server 2008 and above.
USE AdventureWorks2012
GO

EXEC sys.sp_cdc_disable_table
@source_schema = N'HumanResources',
@source_name   = N'Department',
@capture_instance = N'HumanResources_Department'
GO
--OUTPUT

Change data capture.3.3_part3

Step 4 – Disable CDC for database:
In this step, we will disable the CDC feature from any database. Given below is the script that will NOT only disable CDC for any database but also will disable the CDC for all tables in that database. So be careful while executing this statement.

--This script is compatible with SQL Server 2008 and above.

USE AdventureWorks2012
GO

EXEC sys.sp_cdc_disable_db
GO
--OUTPUT

Change data capture.3.4_part3

Let me know if you enabled CDC in your SQL Server and its feedback.

An update of my blog is available at my twitter or you can like my Facebook page or subscribe via email by mentioning your email address in the ‘follow blog’ section.

Read Full Post »

In my earlier article, I have explained how to enable Change Data Capture (CDC) features for any database and its tables in few easy steps. It seems to me that it is very simple and straight forward activity. Just few lines of codes can give you a kick start. The next step is to avail the benefits of CDC, when you perform a series of DML (Data Manipulation Language) statements and CDC records, it automatically?? without a single line of code.

Let me execute INSERT / UPDATE / DELETE statements against the particular tables, which we have enabled for CDC in the earlier article to demonstrate how CDC works.

Please NOTE that before proceeding with CDC you MUST make sure that your SQL SERVER AGENT IS UP AND RUNNING.

  • Impact of INSERT STATEMENT in CDC:

Let me pass an INSERT statement and observe how CDC records its changes in the audit tables as shown below.

--This script is compatible with SQL Server 2008 and above.
USE [AdventureWorks2012]
GO
--Insert a record in [HumanResources].[Department]
INSERT INTO [HumanResources].[Department]
           ([Name]
           ,[GroupName]
           ,[ModifiedDate])
     VALUES
           ('IT'
           ,'R & D'
           ,GETDATE())
GO

Once you insert a new record in the table ([HumanResources].[Department]), lets review the table ([HumanResources].[Department]) and its relevant audit table (cdc.HumanResources_Department_CT) respectively.

--This script is compatible with SQL Server 2008 and above.
USE AdventureWorks2012
GO
SELECT * FROM HumanResources.Department
GO
SELECT * FROM cdc.HumanResources_Department_CT
GO
--OUTPUT

Change data capture.2.1_part2

As you can observe from the above result set, it has one new record in [HumanResources].[Department] as usual. In addition, one record has been inserted in the audit table (cdc.HumanResources_Department_CT) as well with some additional fields and ONE of the most important additional fields is _$operation that will explain you which operation inserted this record in the audit table. In the above result set say, _$operation has a value of 2 and it means that it has been inserted by INSERT statement.

  • Impact of UPDATE STATEMENT in CDC:

Let me pass an UPDATE statement and observe how CDC records its changes in the audit tables as shown below.

--This script is compatible with SQL Server 2008 and above.
USE [AdventureWorks2012]
GO
--Update a record in [HumanResources].[Department]
USE [AdventureWorks2012]
GO
UPDATE [HumanResources].[Department]
   SET [Name] = 'IT Support'
   WHERE [DepartmentID]=17
GO

Once you UPDATE a record in the table ([HumanResources].[Department]), lets review the table ([HumanResources].[Department]) and its relevant audit table (cdc.HumanResources_Department_CT) respectively.

--This script is compatible with SQL Server 2008 and above.
USE AdventureWorks2012
GO
SELECT * FROM HumanResources.Department
GO
SELECT * FROM cdc.HumanResources_Department_CT
GO
--OUTPUT

Change data capture.2.2_part2

As you can observe from the above result set, it has one updated record in [HumanResources].[Department] as usual. In addition, two records have been inserted in the audit table (cdc.HumanResources_Department_CT) as well with some additional fields and ONE of the most important additional fields is _$operation that will explain you which operation inserted this record in the audit table. In the above result set say, _$operation has a value of 3 & 4 and it means that it has been inserted by UPDATE statement. But what do you mean by 3 & 4. In fact 3 means the data before the update statement and 4 means the data after the update statement.

  • Impact of DELETE STATEMENT in CDC:

Let me pass a DELETE statement and observe how CDC records its changes in the audit tables as shown below.

--This script is compatible with SQL Server 2008 and above.
USE [AdventureWorks2012]
GO
--Update a record in [HumanResources].[Department]
USE [AdventureWorks2012]
GO
DELETE from [HumanResources].[Department] WHERE [DepartmentID]=17
GO

Once you DELETE a record in the table ([HumanResources].[Department]), lets review the table ([HumanResources].[Department]) and its relevant audit table (cdc.HumanResources_Department_CT) respectively.

--This script is compatible with SQL Server 2008 and above.
USE AdventureWorks2012
GO
SELECT * FROM HumanResources.Department
GO
SELECT * FROM cdc.HumanResources_Department_CT
GO
--OUTPUT

Change data capture.2.3_part2

As you can observe from the above result set, it has DELETED one record in [HumanResources].[Department] as usual. In addition, ONE record has been inserted in the audit table (cdc.HumanResources_Department_CT) as well with some additional fields and ONE of the most important additional fields is _$operation that will explain you which operation inserted this record in the audit table. In the above result set say, _$operation has a value of 1 and it means that it has been inserted by DELETE statement.

In my upcoming article, I will write how to DISABLE the CDC feature.

An update of my blog is available at my twitter or you can like my Facebook page or subscribe via email by mentioning your email address in the ‘follow blog’ section.

Read Full Post »

In general, if your application having an audit trial feature (to records any add, edit & delete activity performed by any end user in each table), it would be value addition to your application or it may be the best selling point of your application. However, when it comes to the development of this feature, it really become a nightmare. The reason for being problematic is that you need to integrate this feature everywhere in your application. In addition, you need to develop so many audit tables, stored procedures & triggers in order to achieve it. Sometimes the development and testing of this feature takes long time.
In SQL Server 2008, this problem has been solved by shipping a new feature namely Change data capture. It is one of the biggest and remarkable features in SQL Server 2008. In fact, it records and maintains all the changes in any table (if enabled) with very minimal amount of efforts and the good news is that the developers do not need to write even single a line of code to capture all these changes :).

In this article, I will demonstrate how to enable Change Data Capture (CDC) for any database and tables step by step.

Step 1 :
Before enabling CDC for any database, you must check whether it is already enabled or not. Given below is the script that shows all the databases along with its CDC enabled status.

--This script is compatible with SQL Server 2008 and above.
USE master
GO
SELECT
database_id,
name,
is_cdc_enabled
FROM sys.databases
GO
--OUTPUT

Change data capture.1.1_part1

Step 2 :
In step 1, as you observed that all the is_cdc_enabled columns of sys.database (system view) is 0. It means that none of the databases has been enabled for CDC.
Lets enable CDC for AdventureWorks2012 database in this step. Given below is the script.

--This script is compatible with SQL Server 2008 and above.
--DONOT forget to change the database name below.
USE AdventureWorks2012
GO
EXEC sys.sp_cdc_enable_db
GO

Step 3 :
Once you enabled the CDC, you need to repeat step 1, in order to check whether CDC has been enabled for AdventureWorks2012 database or NOT.
Given below is the same script but the output would be different.

--This script is compatible with SQL Server 2008 and above.
USE master
GO
SELECT
database_id,
name,
is_cdc_enabled
FROM sys.databases
GO
--OUTPUT

Change data capture.1.2_part1

As you can see that is_cdc_enabled column value is 1 for AdventureWorks2012 database and it is a confirmation that CDC is enabled for AdventureWorks2012 database.

Step 4 :
Once you enable the CDC in the database lets enable the CDC in a table in order to capture the changes in that particular table.
But hang on a minute, before activating this feature on any table, you must check whether it is already enabled for that particular table or not. Given below is the script that will list down all the tables available in that particular database along with the CDC status.

--This script is compatible with SQL Server 2008 and above.
USE AdventureWorks2012
GO
SELECT
object_id,
SCHEMA_NAME(Schema_id) As [Schema Name],
name As [Table Name],
is_tracked_by_cdc
FROM sys.tables
GO
--OUTPUT

Change data capture.1.4_part1

Step 5 :
In step 5, as you noticed that all the is_cdc_enabled columns of sys.database (system view) is 0. It means that none of the databases has been enabled for CDC.
Lets enable CDC for table namely HumanResources.Department in AdventureWorks2012 database in this step. Given below is the script.

--This script is compatible with SQL Server 2008 and above.
--DONOT forget to change the database name below.
 USE AdventureWorks2012
 GO
 EXEC sys.sp_cdc_enable_table
 @source_schema = N'HumanResources',
 @source_name = N'Department',
 @role_name = NULL
 GO

As you can see above one of the parameters @role_name is NULL. I deliberately made it NULL, so all the users belonging to any role can view the changes in that particular table. However, you can pass any role to restrict the access from other users & roles accordingly.

Step 6 :
In this step, we need to check again if CDC has been enabled for that particular table or not. In order to do it, we need to repeat step 3 and this time the result set would be different. Given below is the same script as Step 4.

--This script is compatible with SQL Server 2008 and above.
USE AdventureWorks2012
GO
SELECT
object_id,
SCHEMA_NAME(Schema_id) As [Schema Name],
name As [Table Name],
is_tracked_by_cdc
FROM sys.tables
GO
--OUTPUT

Change data capture.1.5_part1

Step 7 :
Now, we assured that CDC has been enabled for that particular table, it means that CDC has created an audit table for that particular table (Same way we used to manually create in earlier version of SQL Server to record audit for any table). But where is that particular audit table ? You will find that particular table in CDC schema as shown below.

Change data capture.1.6_part1

In my upcoming article, I will write how to manipulate the CDC feature and how we can view those changes in order to really enjoy the audit trial feature with zero customization.

An update of my blog is available at my twitter or you can like my Facebook page or subscribe via email by mentioning your email address in the ‘follow blog’ section.

Read Full Post »

File table is one of the best features shipped with SQL Server 2012, it is a special type of table which allows us to store files and folders in windows and we can easily access it through windows application & SQL Server without any customization. I have written multiple articles on file table. However, still doing research on this special table. Today, I came across an issue with filetable at the client side once they were doing some activity on it. The issue is when they tried to explore FileTable Directory in a FileTable, the option was disabled as shown below. However I can perform any activity on file table except to explore it.

Explore FileTable Directory.1.1

Now, I cannot explore this file table using explore the filetable directory option. It means that I cannot view the filetable’s files and folder using windows directory. So what is the problem and possible solution ?

Resolution:
I asked the client what activity they did with filetable but they had no clue. So, I started doing my research and finally resolved it by MSDN help. In fact the Filetable’s NON_TRANSACTED_ACCESS has been switched OFF by mistake as shown below.

Explore FileTable Directory.1.2

Wow, I found the problem that has disabled the explore FileTable directory as mentioned above. Now what is the solution ?
Given below is the script that can enable the NON_TRANSACTED_ACCESS that can result in enabling the explore FileTable directory.

--This script is compatible with SQL Server 212 and above.
 ALTER DATABASE SameplDB
    SET FILESTREAM ( NON_TRANSACTED_ACCESS = FULL );
GO

Once you execute the above script, try again to check if the explore FileTable Directory in the FileTable has been enabled or not. This time you will succeed as shown below.

Explore FileTable Directory.1.3

Conclusion:
Whenever, you come across such issue, you must check the NON_TRANSACTED_ACCESS of the particular database in order to fix this issue.

Read Full Post »

In general, upgrading of SQL Server in any latest version is quite complex. However, in my opinion it is NOT at all a rocket science, it is completely based on your planning and understanding about the complexity of your database(s) and its related applications. If you plan and understand it properly, there is no way you cannot make it smooth and up to the mark. In addition, before upgrading it directly on your production database server, you must test it on your test database server like any other application’s deployment and upgrading. But how will I know that is there any problem in my upgrading or NOT ? The solution is one of the best FREE tools that SQL Server provides namely SQL Server Upgrade Advisor. This tool can be downloaded from Microsoft site or it comes with SQL Server 2012 media (Setup files) . The installation of this tool is self explanatory.

SQL Upgrade Advisor

Please note that the database you are planning to upgrade to SQL Server 2012 must have a compatibility of 90 or above. In addition, as much as the gap is between the versions, so is the complexity and problems you can expect. In order to avoid this, it is highly recommended that you should upgrade your SQL Server in a timely manner.  

In this article, I will demonstrate how SQL Server Upgrade Advisor works step by step.

Step 1 : 
Once you install SQL Server 2012 Upgrade Advisor, you will find it in the Start >> Program files as shown below.

upgrade.1.1.1

Step 2 :
Now, it is time to execute the SQL Server Upgrade advisor, it will take you to the first screen, from where you can launch the upgrade Advisor Analysis Wizard. Here you will find the summary about the SQL Server Upgrade Advisor. I strongly recommend that you go through it. Once you review the summary, you need to launch the Upgrade Advsior Analysis Wizard as shown below.

upgrade.1.1

Step 3 :

This is a welcome screen. It will give you the summary of how upgrade Advisor works step by step as shown below. However, you can check mark the Do not show this starting page again in order to avoid this screen recurring, as it is for information ONLY.

upgrade.1.2

Step 4 :

Once you crossed the welcome screen, immediately after that you need to select the components you want to analyse for upgrading. Please make sure that you do NOT  select Notification Services and Data Transformation Services because these two services have been discontinued in the SQL Server 2012. Reporting services you can select if you have installed in that particular server. In my case reporting service was not applicable.

upgrade.1.3

Step 5 :

The next screen will ask you to select the instance and provide the valid credential of that particular instance as shown below.

upgrade.1.4

Step 6 :

The next step is the selection of all or any one databases of the above selected instance that you want to upgrade to SQL Server 2012. Kindly select the appropriate database and press NEXT button.

upgrade.1.5

Step 7 :

Here you need to provide the instance of Analysis services. In addition, you need to provide its credential as well.

upgrade.1.6

Step 8 :

In this step, you need to select the location and the packages of  your integration services. In my case, my SSIS packages are available in the same server so I selected the first option. However, you can select as per your requirement as shown below.

upgrade.1.7

Step 9 :

In this step, SQL upgrade advisor will show you the path of Upgrade advisor report & its log files for your reference.

upgrade.1.8

Step 10 :

Once you press NEXT button, it will start analyzing your selected components on selected databases. It takes 2-30 minutes, depending upon the database size, number of component and performance of your server.

upgrade.1.9

Step 11 :

Once Analysis is done, it will show you a summary report along with the status and a Launch Report button.  Here you need to launch the report, in order to see the results of the SQL Server upgrade analysis for your selected components.

upgrade.1.10

Step 12 :

Once you launch the report, SQL Server Uprade Advisor will show you all the critical issues that you must fix it before or after upgrading. In order to do a smooth migration make sure that you resolve all the given below issues. In my case it is displaying only one issue related to the keyword. In fact I used one word that became a keyword in SQL Server 2012. So I need to replace this keyword.

upgrade.1.12

Step 13 :

Once you select the issue as shown above, it will show where the conflict exactly is. In addition, it will provide all necessary details that you may require at the time of fixing.

upgrade.1.13

 

Let me know about your experience relating to SQL Server upgrade advisor.

Read Full Post »

Full-Text Search is one of the best features shipped with SQL Server 2005. It has all the capabilities that we need for the complex searching scenarios. However, Full- Text Search keep getting the new features in each version of SQL Server. In SQL Server 2012, Full-Text search came up with a brilliant feature called Custom proximity search. Given below are the capabilities of the search.

  1. You can define the maximum number of non search terms or maximum distance between first and last search term
  2. You can also define that search must follow the specific order.

Before I embark on the example, I would like to create a sample to demonstrate this excellent feature.
Sample :

USE AdventureWorks2012
GO
--DROP TABLE [dbo].[tbl_Comments]
--GO
CREATE TABLE [dbo].[tbl_Comments]
(
	[ID] [int] NOT NULL CONSTRAINT [PK_tbl_comments] PRIMARY KEY CLUSTERED ,
	[Comments] [varchar](1000) NULL
 )
GO
INSERT INTO [dbo].[tbl_Comments]
VALUES (1,'This is a demonstration of custom proximity term in Full-Text Search at raresql.com')
GO
--OUTPUT

Please note that once you create the sample, you have to create a Full-Text Index for column “Comments” in the above mentioned table. This article will help you to create Full-Text Index.

Example 1 : (Core functionality)
In this example, we will search two words (proximity and raresql) in the comments column in the sample table. The distance between both words is 6. However, we will increase and decrease the distance between words and observe the output.

--First of all, lets put the the distance 6 and observe the output.
USE AdventureWorks2012
GO
SELECT * FROM [tbl_Comments]
WHERE CONTAINS([Comments], 'NEAR((proximity, raresql), 6)')
--OUTPUT

custom proximity.1.1

--lets reduce the the distance from 6 to 5 words and observe the output.
USE AdventureWorks2012
GO
SELECT * FROM [tbl_Comments]
WHERE CONTAINS([Comments], 'NEAR((proximity, raresql), 5)')
--OUTPUT

custom proximity.1.2

As you can see that once you made the distance less than the actual, it is unable to search. Make sure that you provide whether actual distance or more.

Example 2 : (Implement Order)
In this example, we will search again two words (proximity and raresql) in the comments column.
However, we change the order, but it will not affect the result set until unless you will not forcefully implement the order. By default, it does not implement the order in the search.

--First of all, lets put the the distance 6 and reverse the search words.
--However make the sort order to FALSE (By default, it is false, it is optional to write in the code).
USE AdventureWorks2012
GO
SELECT * FROM [tbl_Comments]
WHERE CONTAINS([Comments], 'NEAR((raresql,proximity), 6,FALSE)')
--OUTPUT

custom proximity.1.3

--Lets put the the distance between the word is 6 and reverse the search words.
--However make the order to TRUE.
USE AdventureWorks2012
GO
SELECT * FROM [tbl_Comments]
WHERE CONTAINS([Comments], 'NEAR((raresql,Search), 6,TRUE)')
--OUTPUT

custom proximity.1.4

As you can see that, first of all, I did not implement the ORDER and made it false. In this case it returned the result set because ORDER did not affect the query. However, once I implemented the search order, it did not return the record because the words are in the opposite order in the comments column of the table. Please make a note that some languages follow left to right direction and vice versa. Custom proximity implement the sort search in the same direction.

Example 3 : (Implement Operator)
In custom proximity search, you have limited numbers of operators like AND, OR & AND NOT operator. However, you can use the combination of any of them. Given below are the samples.

--First of all, let try the AND operator.
USE AdventureWorks2012
GO
SELECT * FROM [tbl_Comments]
WHERE CONTAINS([Comments], 'NEAR((raresql,proximity), 6) And Full-Text')
GO
--OUTPUT

custom proximity.1.5

--Lets try OR operator
USE AdventureWorks2012
GO
SELECT * FROM [tbl_Comments]
WHERE CONTAINS([Comments], 'NEAR((raresql,proximity), 5) OR Full-Text')
GO
--OUTPUT

custom proximity.1.7

--Lets implement AND NOT operator.
USE AdventureWorks2012
GO
SELECT * FROM [tbl_Comments]
WHERE CONTAINS([Comments], 'NEAR((raresql,proximity), 6) AND NOT sample')
GO
--OUTPUT

custom proximity.1.6

As you can see, I implemented three different operators and it works accordingly. However you can use any number of combinations according to your business requirements.

Let me know if you have implemented Custom proximity search in the real world.

Read Full Post »

SQL Server upgrade advisor is one of the important tools and it plays a pivotal role when you need to assess the upgrade in your SQL Server. However, I got an error message when installing SQL Server 2012 upgrade advisor. Given below is the screen image of the error message.

SQL Upgrade Advisor Setup.1.1

Let me explain the error and its solution in detail.

Error Message: 
Setup is missing prerequisites:
-Microsoft SQL Server 2012 Transact-SQL ScriptDom, which is not installed by
Upgrade Advisor Setup. To continue, install SQL Server 2012 Transact-SQL
ScriptDom from below hyperlink and then run the Upgrdae Advisor Setup operation
again :

Go to http://go.microsoft.com/fwlink/?linkID=216742

Resolution:
It seems that it is not only the error message but also the solution as well. Let me resolve it step by step.

Step 1 :
First of all, you need to go to the URL as mentioned above in order to fix this bug.

Step 2 :
Once you browse the above link, it will take you to a page where you need to download the instruction to proceed further. However, you can select the Install instruction link shown below to get the list of all SQL Server tools.

SQL Upgrade Advisor Setup.1.2

Step 3 :
Once you browse install instruction, you need to scroll down till you will find Microsoft® SQL Server® 2012 Transact-SQL ScriptDom. It is available for both (x86 & x64) system types as shown below. However make sure the system type of your server before downloading it. Its installation is self explanatory. Once you install it, you can easily install SQL Server 2012 upgrade advisor.

SQL Upgrade Advisor Setup.1.3

Read Full Post »

Change Data Capture (CDC) is one of the best features shipped with SQL Server 2008. We usually use CDC to record the change of the tables using DML operations for audit purpose. Today, I configured CDC for one of my clients. In order to configure the CDC, first of all, you need to enable it. However, once I tried enabling it but it gave me an error.

Let me explain the error and its solution in detail.

Message Number: 22830

Severity : 16

Error Message: Could not update the metadata that indicates database %s is enabled for Change Data Capture. The failure occurred when executing the command ‘%s’. The error returned was %d: ‘%s’. Use the action and error to determine the cause of the failure and resubmit the request.

Error Generation:
Let me enable the CDC to generate the error.

USE AdventureWorks2012
GO
EXEC sys.sp_cdc_enable_db
GO
--OUTPUT

Msg 22830, Level 16, State 1, Procedure sp_cdc_enable_db_internal, Line 193
Could not update the metadata that indicates database AdventureWorks2012 is enabled for Change Data Capture. The failure occurred when executing the command ‘SetCDCTracked(Value = 1)’. The error returned was 15517: ‘Cannot execute as the database principal because the principal “dbo” does not exist, this type of principal cannot be impersonated, or you do not have permission.’. Use the action and error to determine the cause of the failure and resubmit the request.

Ooopps…… I am unable to enable the CDC. What is the solution ? Let me explain the solution.

Resolution:
If you look at the error message, you will find two error numbers 22830 & 15517. In fact the actual error is 15517 and the reason behind this is, you do not have a valid log in for the database owner of the selected database. The solution I found is from Microsoft support site. Given below is the script.

USE AdventureWorks2012 -- Change the database name
GO
sp_changedbowner 'sa' --Change the valid login name
GO

Once you execute the above statement, you can easily enable the CDC and enjoy the benefit of CDC.

Conclusion:
To conclude, whenever you come across this error, you should change the database owner of the selected database to a valid SQL Server log in.

Read Full Post »

Sometimes lack of knowledge about the product irritates us. No matter, what product we are dealing with.
IntelliSence is one of the marvelous tools, that has been with us Since SQL Server 2008. It really helps us to speed up the development. However, sometimes it irritate as well ;). In SQL Server 2012, IntelliSence came up with two different modes inside to make our life more easier. Given below are the details.

  • Completion Mode
  • Suggestion Mode

Before proceeding with the explanation of these modes, I would like to enable Text Editor Toolbar to toggle between these two modes. Please note that it is not mandatory to enable text editor toolbar for toggling between modes but it is preferable to add because it helps us to see which mode we are in. Let me enable it step by step.

Step 1 :
First of all, you need to open SQL Server Management Studio (SSMS) and select Tools and Customize…  option as shown in the image below.

toggle suggestion and completion mode.2.1

Step 2 :
Once you select Customize… option, it will pop up Customize screen. You need to select Text Editor toolbar in it and press Close as shown below.

toggle suggestion and completion mode.2.2

Step 3 :
In this step, you can see that Text editor toolbar is available in SSMS and Toggles between suggestion and standard completion modes and mode button is highlighted as shown below.

toggle suggestion and completion mode.2.3

Once you enable the Text editor toolbar, let me explain what happens with each mode, with examples.

  • Completion mode : All of us are aware of completion mode because in earlier days completion mode was selected by default in IntelliSence. However, the problem with this mode is, if the object names are symmetrical in database (not necessary) and we search for desired object and by mistake if the criteria matches the other object name (not required), it will select the other object automatically and if this activity is continuous, it irritates us. Please note that, this mode is enabled by default in SQL Server. Given below is the screen image of completion mode in which Toggles between suggestion and standard completion modes button is switched OFF and it means that we are in completion mode.

toggle suggestion and completion mode.1.1

  • Suggestion Mode : This mode is good for the developers if they are working on symmetrical object name database, and this mode of intelliSence just suggest the object name for us, it does not select it automatically like completion mode. You can Press CTRL + ATL + Space bar to switch in Suggestion mode. Given below is the screen image of suggestion mode in which Toggles between suggestion and standard completion modes button is switched ON and it means you are in suggestion mode.

toggle suggestion and completion mode.1.2

Let me know if you used it in the real world examples and how it helped you.

Read Full Post »

« Newer Posts - Older Posts »