Friday, June 24, 2011

How to perform case sensitive searches in SQL Server?




A default SQL Server installation is case insensitive, which means that SQL Server will not differentiate between upper and lower case characters/letters. That is, "Oracle" is the same as "ORACLE" or "oracle". Let's see what controls this case sensitiveness of SQL Server. In SQL Server 6.5 and 7.0, you can select a 'sort order', while installing SQL Server. Sort order defines how the characters supported by the code page are compared and evaluated. The default sort order is 'case insensitive'. If you want SQL Server to perform case sensitive searches, you need to select binary sort order while installing SQL Server (6.5 or 7.0). In these versions (SQL Server 6.5 and 7.0), sort order is defined at the server level, and cannot be manipulated at the database level. If you want to change the sort order of an existing SQL Server 6.5 or 7.0 instance, you must rebuild the master database using rebuildm utility. Not a pleasant thing to do.

Terminology has changed, and now a sort order is called 'collation' in SQL Server 2000. Also ANSI SQL:99 uses the word 'collation'. By default SQL Server 2000 gets installed with case insensitive collation. You must change the collation of the server while installing, if you want case sensitiveness. But the good news is, SQL Server 2000 lets you specify collation at the database as well as column level. That means, you can have a default SQL Server installation with case insensitive collation, but a specific database or a set of columns can have case sensitive collation.

Lets see why one would want to perform case sensitive searches. Classic example is, password comparisons. People use a combination of upper and lower case (mixed case) characters in their passwords, just to make the passwords difficult to guess. But there is no point in doing that, if the database disregards the case. To keep the users happy, and their passwords secure, programmers prefer case sensitive comparisons in this case.

Here is another example: Some people tend to get the MOST out of their database. Yes, from a performance point of view, a binary sort order or case sensitive collation will perform faster, but only at the expense of the usability. Think about a user searching your table for the phrase "fish and chips", and not being able to find a match, just because, that phrase is actually stored in the database as "Fish and Chips". If this happens to be a database serving an online catalog, you will lose some sales as well. One way of avoiding this problem would be, to store everything in lower case, and covert user input to lower case before comparing with the table data. Too much hassle, isn't it? I wouldn't recommend this approach. Not worth the performance you gain.

Here are some methods you can employ for case sensitive searching in SQL Server:

All the examples in this article, are based on the following table and data. Requirement is to implement case sensitive search for CustID and CustPassword columns:
 CREATE TABLE dbo.Customers ( 	CustID char(8) NOT NULL PRIMARY KEY CLUSTERED, 	CustPassword varchar(15) NOT NULL, 	CustName varchar(25) NOT NULL, 	CustEmail varchar(60) NOT NULL, 	CustAddress varchar(100) NOT NULL ) GO  INSERT INTO dbo.Customers VALUES('USA00001', 'TheUnbreakable', 'John', 'John.Smith@SomeDom.com', '10932,Bigge Rd., Menlo Park, CA')  INSERT INTO dbo.Customers VALUES('USA00002', 'ABCxyz012789', 'Heather', 'HGraham@SomeDom.com', '18 Broadway Av. San Francisco, CA')  INSERT INTO dbo.Customers VALUES('EUR00001', 'MagicNSparkle', 'Vyas', 'VyasKN@SomeDom.com', '18 Grove Mead, Herts, AL09 7JK, UK')  INSERT INTO dbo.Customers VALUES('ASA00001', 'aAbBcCXyZ', 'Franklin', 'FMayer@SomeDom.com', '22 Main Street, Singapore')  INSERT INTO dbo.Customers VALUES('OZ000001', 'DOWNundah', 'Steve', 'SWaugh@SomeDom.com', '321, Cricket Way, Melbourne, Australia') 

--NOTE: I omitted the VALUES list in the above INSERT statements, for simplicity.
--This is not a best practice, and you should always include a VALUES list in your INSERT statements.

Consider the following example, that searches the Customers table for a given Customer ID and password. On a server/database with case insensitive collation, this query will disregard case, and will find a row, as long as the values match, no matter what case is used in the input. Since the @CustID 'usa00001' matches a CustID of 'USA00001' and @CustPassword 'theunbreakable' matches the CustPassword of 'TheUnbreakable', the following query prints 'Customer Found!':

 DECLARE @CustID char(8), @CustPassword varchar(15)  SET @CustID = 'usa00001' SET @CustPassword = 'theunbreakable'  IF EXISTS ( 	SELECT 1 	FROM dbo.Customers 	WHERE 	CustID = @CustID 		AND CustPassword = @CustPassword ) BEGIN 	PRINT 'Customer Found!' END ELSE BEGIN 	PRINT 'Invalid Customer ID or Password!' END GO  

Now our goal is to write queries that perform case sensitive comparisons. I will introduce you to some popular techniques, and show you how to make those methods efficient. It is important to note that, if and when you migrate the following queries onto a case sensitive database, it is better to get rid of the additional bits from the WHERE clause, and compare normally.

Method 1: Converting data to binary type before comparison
(Works in both SQL Server 7.0 and 2000)

When you convert a character to binary or varbinary datatype, that character's ASCII value gets represented in binary. Since, 'A' and 'a' have different ASCII values, when you convert them to binary, the binary representations of these values don't match, and hence the case sensitive behavior.

  DECLARE @CustID char(8), @CustPassword varchar(15)  SET @CustID = 'usa00001' SET @CustPassword = 'theunbreakable'  IF EXISTS ( 	SELECT 1 	FROM dbo.Customers 	WHERE 	CAST(CustID AS varbinary(8)) = CAST(@CustID AS varbinary(8)) 		AND CAST(CustPassword AS varbinary(15)) = CAST(@CustPassword AS varbinary(15)) ) BEGIN 	PRINT 'Customer Found!' END ELSE BEGIN 	PRINT 'Invalid Customer ID or Password!' END GO  

The above example will print 'Invalid Customer ID or Password!', because the input provided is in all lower case, while the data in the table is stored in mixed case. Everything looks good. But if you observe the execution plan for this query (By pressing Ctrl + K in Query Analyzer, or by running SET SHOWPLAN_TEXT ON command), you will see an 'index scan'. An index scan is no good from performance point of view, as it means, scanning the whole index -- which is as bad as a table scan.

If you modify the above query as shown below, you will see an 'index seek', which is faster. You will realize this performance difference easily, on a huge table.

 DECLARE @CustID char(8), @CustPassword varchar(15)  SET @CustID = 'usa00001' SET @CustPassword = 'theunbreakable'  IF EXISTS ( 	SELECT 1 	FROM dbo.Customers 	WHERE 	CAST(CustID AS varbinary(8)) = CAST(@CustID AS varbinary(8)) 		AND CAST(CustPassword AS varbinary(15)) = CAST(@CustPassword AS varbinary(15)) 		AND CustID = @CustID 		AND CustPassword = @CustPassword ) BEGIN 	PRINT 'Customer Found!' END ELSE BEGIN 	PRINT 'Invalid Customer ID or Password!' END GO  

Curious, why the first query scanned the index? Here's why: When a column used in the WHERE clause is enclosed within a function (in this case CAST), the Query Optimizer (QP) cannot predict the outcome of the function in advance, and hence it has to scan the whole index and see if there's a match. So, to avoid the problem we added "AND CustID = @CustID AND CustPassword = @CustPassword" to the WHERE clause, and Optimiser made use of the clustered index.



Method 2: Using the COLLATE clause to dictate the case sensitiveness of the query
(Works only in SQL Server 2000)

The COLLATE clause allows us to specify a particular collation for an expression. In the following examples, we will use COLLATE to make our search case sensitive. We need to specify a case sensitive collation along with COLLATE. The following example uses the collation SQL_Latin1_General_CP1_CS_AS. If you are working with non-English data, choose an appropriate collation.

  DECLARE @CustID char(8), @CustPassword varchar(15)  SET @CustID = 'usa00001' SET @CustPassword = 'theunbreakable'  IF EXISTS ( 	SELECT 1 	FROM dbo.Customers 	WHERE 	CustID = @CustID COLLATE SQL_Latin1_General_CP1_CS_AS 		AND CustPassword = @CustPassword COLLATE SQL_Latin1_General_CP1_CS_AS ) BEGIN 	PRINT 'Customer Found!' END ELSE BEGIN 	PRINT 'Invalid Customer ID or Password!' END GO  

The above example will print 'Invalid Customer ID or Password!', because the input provided is in all lower case, while the data in the table is stored in mixed case. But again, the execution plan shows an index scan. We can turn this into an index seek, by following the same trick, as in Method 1: So, let's rewrite the query:

 DECLARE @CustID char(8), @CustPassword varchar(15)  SET @CustID = 'usa00001' SET @CustPassword = 'theunbreakable'  IF EXISTS ( 	SELECT 1 	FROM dbo.Customers 	WHERE 	CustID = @CustID COLLATE SQL_Latin1_General_CP1_CS_AS 		AND CustPassword = @CustPassword COLLATE SQL_Latin1_General_CP1_CS_AS 		AND CustID = @CustID 		AND CustPassword = @CustPassword ) BEGIN 	PRINT 'Customer Found!' END ELSE BEGIN 	PRINT 'Invalid Customer ID or Password!' END GO  


Method 3: Using BINARY_CHECKSUM function
(Works only in SQL Server 2000)

I've seen many people using BINARY_CHECKSUM function to perform case sensitive searches. BINARY_CHECKSUM() function accepts input, and returns a checksum value for that input. Though this is a useful function for tracking changes and verifying integrity of data, I don't think it is the appropriate for making case sensitive searches. It works for simple comparisons though. Here's an example:

  DECLARE @CustID char(8), @CustPassword varchar(15)  SET @CustID = 'usa00001' SET @CustPassword = 'theunbreakable'  IF EXISTS ( 	SELECT 1 	FROM dbo.Customers 	WHERE 	BINARY_CHECKSUM(CustID) = BINARY_CHECKSUM(@CustID) 		AND BINARY_CHECKSUM(CustPassword) = BINARY_CHECKSUM(@CustPassword) ) BEGIN 	PRINT 'Customer Found!' END ELSE BEGIN 	PRINT 'Invalid Customer ID or Password!' END GO  

The above example will print 'Invalid Customer ID or Password!' and you will see an index scan in the execution plan. The following example will convert that into an index seek.

 DECLARE @CustID char(8), @CustPassword varchar(15)  SET @CustID = 'usa00001' SET @CustPassword = 'theunbreakable'  IF EXISTS ( 	SELECT 1 	FROM dbo.Customers 	WHERE 	BINARY_CHECKSUM(CustID) = BINARY_CHECKSUM(@CustID) 		AND BINARY_CHECKSUM(CustPassword) = BINARY_CHECKSUM(@CustPassword) 		AND CustID = @CustID 		AND CustPassword = @CustPassword ) BEGIN 	PRINT 'Customer Found!' END ELSE BEGIN 	PRINT 'Invalid Customer ID or Password!' END GO  

As you can see, this technique works for comparing smaller strings. But the following script will prove that BINARY_CHECKSUM can return the same checksum value for different input values. This is very bad, especially when it comes to validating user names and passwords. Someone can bypass the authentication/authorization by specifying a password, which is not correct, but produces the same checksum value as the correct password. Try this script and find out why I don't recommend this approach:

 SET NOCOUNT ON  DECLARE @i varchar(500)  CREATE TABLE #t (CharValue varchar(500), BinaryChecksum int)  SET @i = 'A'  WHILE @i <> REPLICATE('A', 500) BEGIN 	INSERT #t SELECT @i, BINARY_CHECKSUM(@i) 	SET @i = @i + 'A' END  SELECT CharValue, COUNT(*) AS 'Times Repeated' FROM #t GROUP BY CharValue SELECT BinaryChecksum, COUNT(*) AS 'Times Repeated' FROM #t GROUP BY BinaryChecksum SELECT	BINARY_CHECKSUM('A') AS [Checksum value for 'A'],  	BINARY_CHECKSUM('AAAAAAAAAAAAAAAAA') AS [Checksum value for 'AAAAAAAAAAAAAAAAA']  DROP TABLE #t  


Method 4: Changing the collation of the column permanently, so that all comparisons are case sensitive by default
(Works only in SQL Server 2000)

SQL Server 2000 lets you specify collation at the column level also. So, you could make your CustID and CustPassword columns case sensitive by default. This saves a lot of effort, as you don't have to employ any special techniques in your queries, to get case sensitive behavior. Here's an example:

 /* Since we have a primary key constraint defined on the CustID column, we cannot alter it directly. First we need to drop the constraint. So we need the constraint name. The following procedure will give you the primary key constraint name */    EXEC sp_help 'dbo.Customers' GO   /* Substitute your primary key constraint name into the following ALTER TABLE command, to drop the constraint */   ALTER TABLE dbo.Customers DROP CONSTRAINT PK__Customers__71F07EBE GO   /* Change the collation of the CustID column */   ALTER TABLE dbo.Customers ALTER COLUMN 	CustID char(8) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL GO   /* Add the primary key constraint back in */   ALTER TABLE dbo.Customers ADD CONSTRAINT PK_Customers PRIMARY KEY(CustID) GO   /* Change the collation of the CustPassword column as well */   ALTER TABLE dbo.Customers ALTER COLUMN 	CustPassword varchar(15) COLLATE SQL_Latin1_General_CP1_CS_AS  NOT NULL GO   /* Now try this query and observe the case sensitive behavior */   DECLARE @CustID char(8), @CustPassword varchar(15)  SET @CustID = 'usa00001' SET @CustPassword = 'theunbreakable'  IF EXISTS ( 	SELECT 1 	FROM dbo.Customers 	WHERE	CustID = @CustID 		AND CustPassword = @CustPassword ) BEGIN 	PRINT 'Customer Found!' END ELSE BEGIN 	PRINT 'Invalid Customer ID or Password!' END GO  


Method 5: Using computed columns
(Works in both SQL Server 7.0 and 2000)

You could add a computed column to your table, that exhibits case sensitivity. A computed column is a virtual column that derives its value from the existing columns. SQL Server 2000 allows you to create an index on a computed column to make searches on these columns faster. We will exploit that feature in this example:

 /* Adding two computed columns -- one for CustID and the other for CustPassword */    ALTER TABLE dbo.Customers ADD CustID_CS AS (CAST(CustID AS varbinary(8))),  CustPassword_CS AS (CustPassword COLLATE SQL_Latin1_General_CP1_CS_AS) GO   /* Create a nonclustered index on the computed column CustID_CS */   CREATE NONCLUSTERED INDEX NC_NI_Customers_CustID ON dbo.Customers(CustID_CS) GO   /* The following example will make use of the index on the computed column to perform case sensitive searches efficiently */   DECLARE @CustID char(8), @CustPassword varchar(15)  SET @CustID = 'usa00001' SET @CustPassword = 'theunbreakable'  IF EXISTS ( 	SELECT 1 	FROM dbo.Customers 	WHERE 	CustID_CS = CAST(@CustID AS varbinary(8)) 		AND CustPassword_CS = @CustPassword ) BEGIN 	PRINT 'Customer Found!' END ELSE BEGIN 	PRINT 'Invalid Customer ID or Password!' END GO  


Method 6: Make use of client side languages like VB or VBScript for case sensitive comparisons
(Works in both SQL Server 7.0 and 2000)

Instead of trying perform case sensitive comparisons on the backend, try taking advantage of your client side applications. This may not be appropriate for all situations, but I'm mentioning it here for the sake of completeness. The following code can be used in a Visual Basic application, to perform case sensitive searches:

 Dim cn As New ADODB.Connection, rs As New ADODB.Recordset Dim CustID As String, CustPassword As String  CustID = "usa00001" CustPassword = "theunbreakable"  cn.Open "Server=(local);Integrated Security=SSPI;Database=tempdb;Provider=SQLOLEDB"  Set rs = cn.Execute("SELECT CustID, CustPassword FROM dbo.Customers WHERE CustId = '" + CustID + "' AND CustPassword = '" + CustPassword + "'")  If CustID = rs("CustID") And CustPassword = rs("CustPassword") Then     MsgBox "Customer Found!" Else     MsgBox "Invalid Customer ID or Password!" End If  Set rs = Nothing Set cn = Nothing  


Hope you find the above techniques useful. Here is some additional information that might help you deal with collations: The following command shows you your SQL Server's default collation:

SELECT SERVERPROPERTY('Collation') AS 'Server Level Collation'

To see your default database collation:

SELECT DATABASEPROPERTYEX('Pubs', 'Collation') AS 'Database Level Collation'

To see column level collations of Customers table:

EXEC sp_help 'dbo.Customers'

To see server level collation settings in SQL Server 2000 as well as the previous versions:

EXEC sp_helpsort

To a listing of all available collations in SQL Server 2000:

SELECT * FROM ::fn_helpcollations()

For further information about specific collations:

SELECT COLLATIONPROPERTY('German_PhoneBook_CI_AS', 'CodePage')

SELECT COLLATIONPROPERTY('French_CI_AS', 'LCID')

SELECT COLLATIONPROPERTY('Latin1_General_CI_AS', 'ComparisonStyle')




Saturday, June 11, 2011

Configuring EclipseME and Eclipse


1. Import the Device Definitions

In order to use EclipseME, you must configure at least one Device Definition. In order to do this, perform the following steps:

  1. Select the Preferences menu item from Eclipse's Window menu.

  2. Expand the J2ME item in the pane to the left and click on Device Management.
    screenshot

  3. Select Import...

  4. In the resulting dialog, select the root directory to be searched to find known devices definitions.

    screenshot

    As you leave the search directory text field or select the Refresh button, EclipseME will begin looking for devices in the specified directory and all subdirectories. As of version 1.5.0 of EclipseME, it is no longer necessary to pick the "perfect" directory in order to have devices imported, as EclipseME should be able to locate them if they reside anywhere within the specified directory. EclipseME will display the devices that are found as they are found. If you wish to stop the search at any time, select the Cancel button.

    After the search has completed, use the checkboxes to select the device definitions to be imported. Only devices that have been imported into your configuration may be used by project definitions and for launching. Finally, choose Finish to complete the import process.

    If EclipseME fails to locate a device definition that you expected would be found, it may be that EclipseME does not support it for the time being. In this case, please feel free to submit an RFE to ask for support for this WTK to be added. See here for how to do this.

  5. When you have successfully completed adding the device, you should see the imported devices in the device management preferences.

    screenshot

    Save the definitions by selecting Finish.

2. Change Eclipse's Debug Settings

Because of some quirks in the wireless toolkits, Sun's in particular, if you are going to debug your MIDlet using Eclipse, you must change several of the default debug settings. To do this:

  1. Select the Preferences menu item from Eclipse's Window menu.

  2. Expand the Java item in the left pane and click on the Debug entry.

  3. Ensure that both Suspend execution on uncaught exceptions and Suspend execution on compilation errors near the top of the dialog are NOT checked.

  4. Increase the Debugger timeout near the bottom of the dialog to at least 15000 ms.

  5. The resulting settings should look something like this:
    screenshot

If you do not make these changes you will get errors when you try to run your MIDlet.

3. Configure ProGuard (Optional)

If you will be using ProGuard to produce obfuscated packages, you will need to configure it into the plug-in. To do this:

  1. Select the Preferences menu item from Eclipse's Window menu.

  2. Expand the J2ME and the Packaging items in the left pane and click on the Obfuscation entry.

  3. Configure the ProGuard Root Directory near the top of the dialog.

  4. Configure any other ProGuard-specific settings that you need. For more information on ProGuard, see the ProGuard SourceForge site.

    Important note for Microsoft Windows Users: By default, ProGuard assumes that you are using an operating system that can distinguish between two file names that differ only in their case (i.e. A.java and a.java are two different files. This is clearly not the case in Microsoft Windows. Windows users should be sure to specify the -dontusemixedcaseclassnames option to ProGuard. If you fail to do this, and if you have more than 26 classes in your project, ProGuard's default use of case-sensitive file names will cause classes to overwrite one another. For safety, beginning with release 0.9.0 of EclipseME, the -dontusemixedcaseclassnames option is included as one of the default arguments to ProGuard. UNIX users with projects with many classes may be able to achieve a small reduction in the final size of their JAR file by removing this option.

  5. The resulting settings should look something like this:
    screenshot

4. Configure EclipseME's Over The Air (OTA) options (Optional)

If you will be using EclipseME to debug your MIDlet in Over The Air (OTA) mode, you may want to adjust the OTA Preferences. To do this:

  1. Select the Preferences menu item from Eclipse's Window menu.

  2. Expand the J2ME item in the left pane and click on the Over The Air entry.

  3. The default settings look like this:
    screenshot



Installing EclipseME itself



1. Decide on your approach

Beginning with version 0.5.5, EclipseME is provided as an Eclipse "archive site." With version 0.5.0 and earlier, one simply unzipped the distribution file into the Eclipse installation directory or plugin directory. This is no longer the installation procedure. Please follow the instructions below instead.

If you are updating EclipseME, you should also use the procedure below. New installations and updates follow the same series of steps.

There are two basic ways of installing or updating EclipseME:

  1. You can simply to use Eclipse's built-in updates feature to directly install or update via EclipseME's update site.
  2. You can download an archive site file manually, and then install from it.

2. Installing via the EclipseME Update site

  1. From the Eclipse Help menu, select Software Updates and then Find and install...

  2. You will be presented with the following dialog:
    screenshot
    Select the Search for new features to install radio button.
    Press Next.

  3. You will next be presented with the following dialog:
    screenshot
    Press the New Remote Site... button.

  4. In the New Update Site dialog that appears, enter a name for the update site and the URL http://www.eclipseme.org/updates/ and press Open.
    screenshot

  5. The update site will now be listed in the Install dialog.
    screenshot
    Ensure that there is a check next to the EclipseME update site, then press Next.

  6. You will next be presented with the following dialog:
    screenshot
    Ensure that there is a check next to EclipseME. Other available features can be installed by checking them as well. Press Next.

  7. The installation process will next display the license agreement for EclipseME.
    screenshot
    Select I accept the terms of the license agreements and press Next.

  8. The next dialog that appears displays the possible locations into which you can install EclipseME. screenshot
    In many cases, the only site that will be listed will be the main Eclipse installation directory. If you have other plugins or features installed, however, you may see additional sites.

    Although you may install EclipseME elsewhere, we recommend that you install it in your main Eclipse installation directory.

    Once you have selected a site, press Finish.

  9. At present the EclipseME package is not digitally signed. (Maintaining the keys required to digitally sign JAR files costs $400+/year. If anyone is interested in funding EclipseME to this extent, we'll be happy to sign the JAR files.) As a result, the following warning dialog will be displayed:
    screenshot
    Presuming that you are certain that you obtained the EclipseME distribution file from a reputable source (such as directly downloading it yourself from SourceForge.net), it should be safe to trust the installation package.

    If you choose to continue, press the Install button.

  10. At this point, Eclipse will begin to install EclipseME from the site archive file. When the installation is complete, you will see one of the following two dialogs:

    New installations screenshot
    Updates screenshot

    Even though the dialog shown during new installations suggests that it may be possible to continue without restarting, you should definitely restart Eclipse at this point.

    Once Eclipse restarts, the installation procedure is complete.

    In the vast majority of the cases, the installation procedure above will properly handle updating a previous version of EclipseME.

    Under some rare circumstances, even after Eclipse restarts, EclipseME will not end up properly "registered" inside Eclipse. This can generally be corrected by closing Eclipse and then restarting Eclipse adding the -clean parameter to the other parameters you normally use. Using the -clean option forces Eclipse to rescan and update all its plugin information.

    If the plug-in is properly installed, there will be a J2ME entry in the Window / Preferences dialog.
    screenshot

3. Installing via a downloaded archive site

The process of installing EclipseME via a downloaded archive file is very similar to that of installing it via the EclipseME update site.

  1. Download the latest archive site from the EclipseME downloads page.

  2. From the Eclipse Help menu, select Software Updates and then Find and install...

  3. You will be presented with the following dialog:
    screenshot
    Select the Search for new features to install radio button. You should select this option even if you are updating EclipseME.
    Press Next.

  4. You will next be presented with the following dialog:
    screenshot
    Press the New Archived Site... button.

  5. In the Select Local Site Archive dialog that appears, select the distribution ZIP file and press Open.
    screenshot

  6. The site archive file will now be listed in the Install dialog.
    screenshot
    Ensure that there is a check next to the distribution file, then press Next.

  7. You will next be presented with the following dialog:
    screenshot
    Ensure that there is a check next to EclipseME. Other available features can be installed by checking them as well. Press Next.

  8. The installation process will next display the license agreement for EclipseME.
    screenshot
    Select I accept the terms of the license agreements and press Next.

  9. The next dialog that appears displays the possible locations into which you can install EclipseME. screenshot
    In many cases, the only site that will be listed will be the main Eclipse installation directory. If you have other plugins or features installed, however, you may see additional sites.

    Although you may install EclipseME elsewhere, we recommend that you install it in your main Eclipse installation directory.

    Once you have selected a site, press Finish.

  10. At present the EclipseME package is not digitally signed. (Maintaining the keys required to digitally sign JAR files costs $400+/year. If anyone is interested in funding EclipseME to this extent, we'll be happy to sign the JAR files.) As a result, the following warning dialog will be displayed:
    screenshot
    Presuming that you are certain that you obtained the EclipseME distribution file from a reputable source (such as directly downloading it yourself from SourceForge.net), it should be safe to trust the installation package.

    If you choose to continue, press the Install button.

  11. At this point, Eclipse will begin to install EclipseME from the site archive file. When the installation is complete, you will see one of the following two dialogs:

    New installations screenshot
    Updates screenshot

    Even though the dialog shown during new installations suggests that it may be possible to continue without restarting, you should definitely restart Eclipse at this point.

    Once Eclipse restarts, the installation procedure is complete.

    In the vast majority of the cases, the installation procedure above will properly handle updating a previous version of EclipseME.

    Under some rare circumstances, even after Eclipse restarts, EclipseME will not end up properly "registered" inside Eclipse. This can generally be corrected by closing Eclipse and then restarting Eclipse adding the -clean parameter to the other parameters you normally use. Using the -clean option forces Eclipse to rescan and update all its plugin information.

    If the plug-in is properly installed, there will be a J2ME entry in the Window / Preferences dialog.
    screenshot

4. Configuring Preprocessor Support

Due to limitations in the extensibility of the Eclipse Java Compiler, it is necessary for EclipseME to hook into the Eclipse platform at a lower level in order to provide preprocessor support. If you would like to take advantage of EclipseME's support for preprocessing, a few extra changes need to be made to your Eclipse installation.

  1. When installing the EclipseME feature, it is imperative that it be installed into the same directory structure as your base Eclipse installation. For instance, if your base Eclipse installation (not your workspace) is installed to c:\software\eclipse, you will need to make sure that you install EclipseME into c:\software\eclipse. When the installation has completed, the eclipseme.core.hooks_[version].jar file must be located in the same directory as the org.eclipse.org_[version].jar.
  2. The Eclipse configuration file must be altered to reference the EclipseME framework hooks bundle.
    1. Locate the file config.ini. This file is located in the <eclipse install>/configuration directory.
    2. Edit the config.ini with your favorite text editor.
    3. Add the property definition
      osgi.framework.extensions=eclipseme.core.hooks
      to the configuration file.
      If the property already exists, append ",eclipseme.core.hooks" to the end of the current property definition.

      It is very important that the line be added before the end-of-file marker. Failure to properly place the new line before the end-of-file marker could cause your Eclipse workbench to fail to start.
    4. Save the configuration file.
    5. Restart Eclipse.

You will be warned if you have projects that are configured for preprocessing, but the necessary configuration changes have not been made. In this case, a warning will be written to the Eclipse workspace log file with text similar to
"Preprocessor invoked, but hook is not installed. Consult the installation instructions for EclipseME."
For more information on viewing the workspace log file see the troubleshooting section of the EclipseME documentation.



Thursday, June 9, 2011

SQL SERVER – Search String in Stored Procedure


SQL Server has released SQL Server 2000 edition before 7 years and SQL Server 2005 edition before 2 years now. There are still few users who have not upgraded to SQL Server 2005 and they are waiting for SQL Server 2008 in February 2008 to SQL Server 2008 to release. This blog has is heavily visited by users from both the SQL Server products. I have two previous post which demonstrates the code which can be search string in stored procedure. Many users get confused with the script version and try to execute SQL Server 2005 version on SQL Server 2000, they do send me email or leave comment that this does not work. I am going to list both the post here with clearly indicating the SQL Server version. I am sure this will clear some of the doubts.

SQL Server 2000
USE AdventureWorks
GO
--Option 1
SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%Employee%'
GO
--Option 2
SELECT DISTINCT o.name ,o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%Employee%'
GO

SQL Server 2005
USE AdventureWorks
GO
--Searching for Empoloyee table
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Employee%'
GO
--Searching for Empoloyee table and RateChangeDate column together
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Employee%'
AND OBJECT_DEFINITION(OBJECT_ID) LIKE '%RateChangeDate%'
GO



Tuesday, June 7, 2011

An Introduction To Linked Servers



A linked server configuration enables SQL Server to execute commands against OLE DB data sources on remote servers. Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T-SQL Statements.
Linked servers offer the following advantages:


1. Remote server access.
2. The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
3. The ability to address diverse data sources similarly.

With a linked server, you can create very clean, easy to follow, SQL statements that allow remote data to be retrieved, joined and combined with local data. Stored Procedure sp_addlinkedserver, sp_addlinkedsrvlogin will be used add new Linked Server.

-------------------------------------------------------------------------------

What are Linked Servers?

A Linked Server is a Link to an external (remote) data source. The remote data source can be Oracle, Access, Excel or any other data source that uses an OLE DB provider.

What are Remote Servers?

  • Remote servers do not use OLE DB providers
  • A remote server configuration allows a client connected to one instance of SQL Server to execute a stored procedure on another instance of SQL Server without establishing another connection
  • Both stored procedures and distributed queries are allowed against linked servers; however, only stored procedures are allowed against remote servers.
  • Use Linked Servers instead of Remote Servers. Remote servers are for backward compatibility only.

Note: Linked servers can run remote stored procedures for SQL Server and cannot run remote stored procedures for Oracle.

Adding Linked Servers

Syntax

sp_addlinkedserver [ @server = ] 'server'
    
[ , [ @srvproduct = ] 'product_name' ]
    [ , [ @provider = ] 'provider_name' ]
    [ , [ @datasrc = ] 'data_source' ]
    [ , [ @location = ] 'location' ]
    [ , [ @provstr = ] 'provider_string' ]
    [ , [ @catalog = ] 'catalog' ]

Please refer BOL for syntax and more info

Example: To add Oracle as a linked server to SQL Server:

This example creates a linked server named OraLinkServ that uses the Microsoft OLE DB Provider for Oracle and assumes that the SQL*Net alias for the Oracle database is OraProduction.

USE master GO EXEC sp_addlinkedserver     @server = 'OraLinkServ',    @srvproduct = 'Oracle',    @provider = 'MSDAORA',    @datasrc = 'OraProduction' GO
 

Adding Linked Server Login

Sp_addlinkedsvrlogin

Creates or updates a mapping between logins on the local instance of SQL Server and remote logins on the linked server.

Syntax

sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'
    [ , [ @useself = ] 'useself' ]
    
[ , [ @locallogin = ] 'locallogin' ]
    [ , [ @rmtuser = ] 'rmtuser' ]
    
[ , [ @rmtpassword = ] 'rmtpassword' ]

Please refer BOL for syntax and more info

Querying Linked Server

The following are the various methods of querying different linked servers.

LinkedServerName.Dbname.Owner.ObjectName

Select * from LinkSqlServ.Northwind.dbo.Employees

Select * from OraLinkServ..SCOTT.Stores 

Select * from LinkMdb...customers

OpenQuery: - Executes the specified pass-through query on the given linked server, which is an OLE DB data source.

Syntax:

OpenQuery ( linked_server, 'query' )

Examples:

SELECT * FROM OPENQUERY(OraLinkServ, 'SELECT Name, Title FROM Scott.Titles')   INSERT OPENQUERY(LinkSqlServ, 'select * from pubs.dbo.jobs') values (15, 'Technical Editor', 100, 300) 

OpenRowSet: - This method is an adhoc method of connecting and accessing remote data using OLE DB. It creates linked server on the fly.

Syntax

OPENROWSET ( 'provider_name'
    ,
{ 'datasource' ; 'user_id' ; 'password'
        | 'provider_string' }
    , { [ catalog. ] [ schema. ] object
        | 'query' }
    )

Removing Linked Server and its Logins

Sp_dropserver - Removes a server from the list of known remote and linked servers on the local SQL Server.

Sp_droplinkedsvrlogin: Removes an existing mapping between a login on the local server running SQL Server and a login on the linked server.

Obtaining Meta data

Please refer BOL for complete syntax.

Sp_tables_ex: Returns table information about the tables from the specified linked server.

sp_columns_ex: Returns the column information, one row per column, for the given linked server table(s). sp_columns_ex returns column information only for the given column if column is specified.

sp_table_privileges_ex: Returns privilege information about the specified table from the specified linked server.

sp_column_privileges_ex: Returns column privileges for the specified table on the specified linked server.

Sp_linkedservers:Returns the list of linked servers defined in the local server.

Sp_helpserver: Reports information about a particular remote or replication server, or about all servers of both types. Provides the server name, the server's network name, the server's replication status, the server's identification number, collation name, and time-out values for connecting to, or queries against, linked servers.

Sysservers: Contains one row for each server that SQL Server can access as an OLE DB data source. This table is stored in the master database.

Sysoledbusers: Contains one row for each user and password mapping for the specified linked server. This table is stored in the master database.

xp_enum_oledb_providers: Provides information about OLEDB providers.

Sp_serveroption: Sets server options for remote servers and linked servers.

Sp_serveroption has been enhanced with two new options, use remote collation and collation name, that support collations in linked servers.

Sp_setnetname: Sets the network names in sysservers to their actual network computer names for remote instances of  SQL Server. This procedure can be used to enable execution of remote stored procedure calls to computers that have network names containing invalid SQL Server identifiers.


What is a Linked Server?

Think of a Linked Server as an alias on your local SQL server that points to an external data source. This external data source can be Access, Oracle, Excel or almost any other data system that can be accessed by OLE or ODBC--including other MS SQL servers. An MS SQL linked server is similar to the MS Access feature of creating a "Link Table."

Why use a Linked Server?

With a linked server, you can create very clean, easy to follow, SQL statements that allow remote data to be retrieved, joined and combined with local data.

While it would be convenient to have all of our business data in one place, there are too many obstacles such as Vendor applications built for a specific data store, data sets too large for one server, legacy flat file applications that are cost prohibitive to recreate and changing business standards, preventing this from happening.

"Replication Manager" has made moving data from one SQL Server to another on a regular basis relatively easy. However, duplicating data to an application server is not always the best solution. If your source is large, and you cannot predict what subset of data you will need, then a linked server may be a better solution.

If you have a very large data set, there may be performance benefits to splitting your data into pieces, and moving those pieces onto different servers. Then using distributed partitioned views to present the data as one source. If so, linked servers are the technology that makes it possible.

Why not use a Linked Server?

If the remote data is not yours, and the owning department will not allow you remote access, then a linked server is out. You will have to rely on some type of scheduled pickup and exchange.

When absolute, best possible performance is required, local data will out perform a linked server.

If the physical link between your SQL Server and the remote data is slow, or not reliable, then a linked server is not a good solution.

History

Linked servers are a superset of "remote servers." Remote servers allowed the running of stored procedures on distributed SQL Server machines. SQL 2000 BOL states, "Support for remote servers is provided for backward compatibility only. New applications that must execute stored procedures against remote instances of SQL Server should use linked servers instead." Support for remote servers may be discontinued in the future. In addition, remote servers only allowed stored procedures to be run. Linked servers allow both stored procedures and ad hoc queries.

Distributed Transaction Coordinator (DTC)

Before starting the examples, we need to start the Distributed Transaction Coordinator. The DTC manages the committing of transactions when there are several different data sources involved. For Windows 2000, service pack 1 is required.

+ Open the services MMC, locate and start the Distributed Transaction Coordinator using the default settings.


I am making assumptions that remote server is already set up as linked server with proper permissions in application and network is arranged.

Method 1 : Remote Stored Procedure can be called as four part name:
Syntax:

EXEC [RemoteServer] .DatabaseName.DatabaseOwner.StoredProcedureName

'Params'
Example:

EXEC [DEVSQL] .AdventureWorks.dbo.uspGetEmployeeManagers '42′

Method 2 : Use Distributed Query functions (OpenQuery())
Syntax: SELECT *

FROM OPENQUERY([RemoteServer],DatabaseName.DatabaseOwner.StoredProcedureName)

Example: SELECT *

FROM OPENQUERY([DEVSQL],AdventureWorks.dbo.spAdmin_GetAllUsers)

Let me know if you want me write about above two methods in detail.


SQL SERVER – Interview Questions and Answers – Part 8



What is Data Compression?

In SQL SERVE 2008 Data Compression comes in two flavors:

  • Row Compression
  • Page Compression

Row Compression

Row compression changes the format of physical storage of data. It minimize the metadata (column information, length, offsets etc) associated with each record. Numeric data types and fixed length strings are stored in variable-length storage format, just like Varchar.  

Page Compression

Page compression allows common data to be shared between rows for a given page. Its uses the following techniques to compress data:

  • Row compression.
  • Prefix Compression. For every column in a page duplicate prefixes are identified. These prefixes are saved in compression information headers (CI) which resides after page header. A reference number is assigned to these prefixes and that reference number is replaced where ever those prefixes are being used.

Dictionary Compression.

Dictionary compression searches for duplicate values throughout the page and stores them in CI. The main difference between prefix and dictionary compression is that prefix is only restricted to one column while dictionary is applicable to the complete page.

What is use of DBCC Commands?

The Transact-SQL programming language provides DBCC statements that act as Database Console Commands for SQL Server.  DBCC commands are used to perform following tasks.

  • Maintenance tasks on database, index, or filegroup.
  • Tasks that gather and display various types of information.
  • Validation operations on a database, table, index, catalog, filegroup, or allocation of database pages.
  • Miscellaneous tasks such as enabling trace flags or removing a DLL from memory.


How to find tables without Indexes?

Run following query in Query Editor.

USE <database_name>;
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name
,name AS table_name
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'IsIndexed') = 0
ORDER BY schema_name, table_name;
GO

How to copy the tables, schema and views from one SQL Server to another?

There are multiple ways to do this.

  1. "Detach Database" from one server and "Attach Database" to another server.
  2. Manually script all the objects using SSMS and run the script on new server.
  3. Use Wizard of SSMS.

How to copy data from one table to another table?

There are multiple ways to do this.

1) INSERT INTO SELECT

This method is used when table is already created in the database earlier and data is to be inserted into this table from another table. If columns listed in insert clause and select clause are same, they are not required to list them.

2) SELECT INTO

This method is used when table is not created earlier and needs to be created when data from one table is to be inserted into newly created table from another table. New table is created with same data types as selected columns.


What is Catalog Views?

Catalog views return information that is used by the SQL Server Database Engine. Catalog Views are the most general interface to the catalog metadata and provide the most efficient way to obtain, transform, and present customized forms of this information. All user-available catalog metadata is exposed through catalog views.

What is PIVOT and UNPIVOT?

A Pivot Table can automatically sort, count, and total the data stored in one table or spreadsheet and create a second table displaying the summarized data. The PIVOT operator turns the values of a specified column into column names, effectively rotating a table.

UNPIVOT table is reverse of PIVOT Table. 

What is Filestream?

Filestream allows you to store large objects in the file system and have these files integrated within the database. It enables SQL Server based applications to store unstructured data such as documents, images, audios, videos etc. in the file system. FILESTREAM basically integrates the SQL Server Database Engine with New Technology File System (NTFS); it basically stores the data in varbinary (max) data type. Using this data type, the unstructured data is stored in the NTFS file system and the SQL Server Database Engine manages the link between the Filestream column and the actual file located in the NTFS. Using Transact SQL statements users can insert, update, delete and select the data stored in FILESTREAM enabled tables.

What is Dirty Read ?

A dirty read occurs when two operations say, read and write occurs together giving the incorrect or unedited data. Suppose, A has changed a row, but has not committed the changes. B reads the uncommitted data but his view of the data may be wrong so that is Dirty Read.

What is SQLCMD?

sqlcmd is enhanced version of the isql and osql and it provides way more functionality than other two options. In other words sqlcmd is better replacement of isql (which will be deprecated eventually) and osql (not included in SQL Server 2005 RTM). sqlcmd can work two modes – i) BATCH and ii) interactive modes. 

What is Aggregate Functions?

Aggregate functions perform a calculation on a set of values and return a single value. Aggregate functions ignore NULL values except COUNT function. HAVING clause is used, along with GROUP BY, for filtering query using aggregate values.

Following functions are aggregate functions.

AVG, MIN, CHECKSUM_AGG, SUM, COUNT, STDEV, COUNT_BIG, STDEVP, GROUPING, VAR, MAX, VARP

What do you mean by Table Sample?
TABLESAMPLE allows you to extract a sampling of rows from a table in the FROM clause. The rows retrieved are random and they are not in any order. This sampling can be based on a percentage of number of rows. You can use TABLESAMPLE when only a sampling of rows is necessary for the application instead of a full result set.

What is Row_Number()?
ROW_NUMBER() returns a column as an expression that contains the row's number within the result set. This is only a number used in the context of the result set, if the result changes, the ROW_NUMBER() will change.

What are Ranking Functions?

Ranking functions return a ranking value for each row in a partition. All the ranking functions are non-deterministic. Different Ranking functions are:

ROW_NUMBER () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

RANK () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the rank of each row within the partition of a result set.

DENSE_RANK () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the rank of rows within the partition of a result set, without any gaps in the ranking.

What is the difference between UNION and UNION ALL?

UNION
The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.

UNION ALL
The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.

The difference between Union and Union all is that Union all will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table. 

What is B-Tree?

The database server uses a B-tree structure to organize index information. B-Tree generally has following types of index pages or nodes:

  • root node: A root node contains node pointers to branch nodes which can be only one.
  • branch nodes: A branch node contains pointers to leaf nodes or other branch nodes which can be two or more.
  • leaf nodes: A leaf node contains index items and horizontal pointers to other leaf nodes which can be many.


SQL SERVER – Interview Questions and Answers – Part 7



How can we rewrite sub-queries into simple select statements or with joins?

Yes we can write using Common Table Expression (CTE). A Common Table Expression (CTE) is an expression that can be thought of as a temporary result set which is defined within the execution of a single SQL statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query.

E.g.

USE AdventureWorks
GO
WITH EmployeeDepartment_CTE AS (
SELECT EmployeeID,DepartmentID,ShiftID
FROM HumanResources.EmployeeDepartmentHistory
)
SELECT ecte.EmployeeId,ed.DepartmentID, ed.Name,ecte.ShiftID
FROM HumanResources.Department ed
INNER JOIN EmployeeDepartment_CTE ecte ON ecte.DepartmentID = ed.DepartmentID
GO

What is CLR?

In SQL Server 2008, SQL Server objects such as user-defined functions can be created using such CLR languages. This CLR language support extends not only to user-defined functions, but also to stored procedures and triggers. You can develop such CLR add-ons to SQL Server using Visual Studio 2008. 

What are synonyms?

Synonyms give you the ability to provide alternate names for database objects. You can alias object names; for example, using the Employee table as Emp. You can also shorten names. This is especially useful when dealing with three and four part names; for example, shortening server.database.owner.object to object.

What is LINQ?

Language Integrated Query (LINQ) adds the ability to query objects using .NET languages. The LINQ to SQL object/relational mapping (O/RM) framework provides the following basic features:

  • Tools to create classes (usually called entities) mapped to database tables
  • Compatibility with LINQ's standard query operations
  • The DataContext class, with features such as entity record monitoring, automatic SQL statement generation, record concurrency detection, and much more

What is Isolation Levels?

Transactions specify an isolation level that defines the degree to which one transaction must be isolated from resource or data modifications made by other transactions. Isolation levels are described in terms of which concurrency side-effects, such as dirty reads or phantom reads, are allowed.

Transaction isolation levels control:

  • Whether locks are taken when data is read, and what type of locks are requested.
  • How long the read locks are held.
  • Whether a read operation referencing rows modified by another transaction:
  • Blocks until the exclusive lock on the row is freed.
  • Retrieves the committed version of the row that existed at the time the statement or transaction started.
  • Reads the uncommitted data modification. 

What is use of EXCEPT Clause?

EXCEPT clause is similar to MINUS operation in Oracle. The EXCEPT query and MINUS query returns all rows in the first query that are not returned in the second query. Each SQL statement within the EXCEPT query and MINUS query must have the same number of fields in the result sets with similar data types.

What is XPath?

XPath uses a set of expressions to select nodes to be processed. The most common expression that you'll use is the location path expression, which returns back a set of nodes called a node set. XPath can use both an unabbreviated and an abbreviated syntax. The following is the unabbreviated syntax for a location path:

/axisName::nodeTest[predicate]/axisName::nodeTest[predicate]

What is NOLOCK?

Using the NOLOCK query optimizer hint is generally considered good practice in order to improve concurrency on a busy system. When the NOLOCK hint is included in a SELECT statement, no locks are taken when data is read. The result is a Dirty Read, which means that another process could be updating the data at the exact time you are reading it. There are no guarantees that your query will retrieve the most recent data. The advantage to performance is that your reading of data will not block updates from taking place, and updates will not block your reading of data. SELECT statements take Shared (Read) locks. This means that multiple SELECT statements are allowed simultaneous access, but other processes are blocked from modifying the data. The updates will queue until all the reads have completed, and reads requested after the update will wait for the updates to complete. The result to your system is delay (blocking).

How would you handle error in SQL SERVER 2008?

SQL Server now supports the use of TRY…CATCH constructs for providing rich error handling. TRY…CATCH lets us build error handling at the level we need, in the way we need to, by setting a region where if any error occurs, it will break out of the region and head to an error handler. The basic structure is as follows:

BEGIN TRY

<code>

END TRY

BEGIN CATCH

<code>

END CATCH

So if any error occurs in the TRY block, execution is diverted to the CATCH block, and the error can be dealt.

What is RAISEERROR?

RaiseError generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message stored in the sys.messages catalog view or build a message dynamically. The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY…CATCH construct.

How to rebuild Master Databse?

Master database is system database and it contains information about running server's configuration. When SQL Server 2005 is installed it usually creates master, model, msdb, tempdb resource and distribution system database by default. Only Master database is the one which is absolutely must have database. Without Master database SQL Server cannot be started. This is the reason it is extremely important to backup Master database.

To rebuild the Master database, Run Setup.exe, verify, and repair a SQL Server instance, and rebuild the system databases. This procedure is most often used to rebuild the master database for a corrupted installation of SQL Server.

What is XML Datatype?

The xml data type lets you store XML documents and fragments in a SQL Server database. An XML fragment is an XML instance that is missing a single top-level element. You can create columns and variables of the xml type and store XML instances in them. The xml data type and associated methods help integrate XML into the relational framework of SQL Server.



SQL SERVER – Interview Questions and Answers – Part 6


3) Questions of SQL SERVER 2008

What are the basic functions for master, msdb, model, tempdb and resource databases?

The master database holds information for all databases located on the SQL Server instance and is theglue that holds the engine together. Because SQL Server cannot start without a functioning masterdatabase, you must administer this database with care.

The msdb database stores information regarding database backups, SQL Agent information, DTS packages, SQL Server jobs, and some replication information such as for log shipping.

The tempdb holds temporary objects such as global and local temporary tables and stored procedures.

The model is essentially a template database used in the creation of any new user database created in the instance.

The resoure Database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata.

What is Service Broker?

Service Broker is a message-queuing technology in SQL Server that allows developers to integrate SQL Server fully into distributed applications. Service Broker is feature which provides facility to SQL Server to send an asynchronous, transactional message. it allows a database to send a message to another database without waiting for the response, so the application will continue to function if the remote database is temporarily unavailable. 

Where SQL server user names and passwords are stored in SQL server?

They get stored in System Catalog Views sys.server_principals and sys.sql_logins.

What is Policy Management?

Policy Management in SQL SERVER 2008 allows you to define and enforce policies for configuring and managing SQL Server across the enterprise. Policy-Based Management is configured in SQL Server Management Studio (SSMS).  Navigate to the Object Explorer and expand the Management node and the Policy Management node; you will see the Policies, Conditions, and Facets nodes.

What is Replication and Database Mirroring?

Database mirroring can be used with replication to provide availability for the publication database. Database mirroring involves two copies of a single database that typically reside on different computers. At any given time, only one copy of the database is currently available to clients which are known as the principal database. Updates made by clients to the principal database are applied on the other copy of the database, known as the mirror database. Mirroring involves applying the transaction log from every insertion, update, or deletion made on the principal database onto the mirror database.

What are Sparse Columns?

A sparse column is another tool used to reduce the amount of physical storage used in a database. They are the ordinary columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve nonnull values. 

What does TOP Operator Do?

The TOP operator is used to specify the number of rows to be returned by a query. The TOP operator has new addition in SQL SERVER 2008 that it accepts variables as well as literal values and can be used with INSERT, UPDATE, and DELETES statements.

What is CTE?

CTE is an abbreviation Common Table Expression. A Common Table Expression (CTE) is an expression that can be thought of as a temporary result set which is defined within the execution of a single SQL statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query.

What is MERGE Statement?
MERGE is a new feature that provides an efficient way to perform multiple DML operations. In previous versions of SQL Server, we had to write separate statements to INSERT, UPDATE, or DELETE data based on certain conditions, but now, using MERGE statement we can include the logic of such data modifications in one statement that even checks when the data is matched then just update it and when unmatched then insert it. One of the most important advantages of MERGE statement is all the data is read and processed only once.  

What is Filtered Index?

Filtered Index is used to index a portion of rows in a table that means it applies filter on INDEX which improves query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes. When we see an Index created with some where clause then that is actually a FILTERED INDEX.

Which are new data types introduced in SQL SERVER 2008?

The GEOMETRY Type: The GEOMETRY data type is a system .NET common language runtime (CLR) data type in SQL Server. This type represents data in a two-dimensional Euclidean coordinate system.

The GEOGRAPHY Type: The GEOGRAPHY datatype's functions are the same as with GEOMETRY. The difference between the two is that when you specify GEOGRAPHY, you are usually specifying points in terms of latitude and longitude.

New Date and Time Datatypes: SQL Server 2008 introduces four new datatypes related to date and time: DATE, TIME, DATETIMEOFFSET, and DATETIME2.

  • DATE: The new DATE type just stores the date itself. It is based on the Gregorian calendar and handles years from 1 to 9999.
  • TIME: The new TIME (n) type stores time with a range of 00:00:00.0000000 through 23:59:59.9999999. The precision is allowed with this type. TIME supports seconds down to 100 nanoseconds. The n in TIME (n) defines this level of fractional second precision, from 0 to 7 digits of precision.
  • The DATETIMEOFFSET Type: DATETIMEOFFSET (n) is the time-zone-aware version of a datetime datatype. The name will appear less odd when you consider what it really is: a date + a time + a time-zone offset. The offset is based on how far behind or ahead you are from Coordinated Universal Time (UTC) time.
  • The DATETIME2 Type: It is an extension of the datetime type in earlier versions of SQL Server. This new datatype has a date range covering dates from January 1 of year 1 through December 31 of year 9999. This is a definite improvement over the 1753 lower boundary of the datetime datatype. DATETIME2 not only includes the larger date range, but also has a timestamp and the same fractional precision that TIME type provides

What are the Advantages of using CTE?

  • Using CTE improves the readability and makes maintenance of complex queries easy.
  • The query can be divided into separate, simple, logical building blocks which can be then used to build more complex CTEs until final result set is generated.
  • CTE can be defined in functions, stored procedures, triggers or even views.
  • After a CTE is defined, it can be used as a Table or a View and can SELECT, INSERT, UPDATE or DELETE Data.


SQL SERVER – Interview Questions and Answers – Part 5


What command do we use to rename a db, a table and a column?

To rename db

sp_renamedb 'oldname' , 'newname'

If someone is using db it will not accept sp_renmaedb. In that case first bring db to single user using sp_dboptions. Use sp_renamedb to rename database. Use sp_dboptions to bring database to multi user mode.

E.g.

USE master;

GO

EXEC sp_dboption AdventureWorks, 'Single User', True

GO

EXEC sp_renamedb 'AdventureWorks', 'AdventureWorks_New'

GO

EXEC sp_dboption AdventureWorks, 'Single User', False

GO

To rename Table

We can change the table name using sp_rename as follows,

sp_rename 'oldTableName' 'newTableName'

E.g.

sp_RENAME 'Table_First', 'Table_Last'
GO

To rename Column

The script for renaming any column :

sp_rename 'TableName.[OldcolumnName]', 'NewColumnName', 'Column'
E.g.
sp_RENAME 'Table_First.Name', 'NameChange' , 'COLUMN'
GO

What are sp_configure commands and set commands?

Use sp_configure to display or change server-level settings. To change database-level settings, use ALTER DATABASE. To change settings that affect only the current user session, use the SET statement.

E.g.

sp_CONFIGURE 'show advanced', 0
GO
RECONFIGURE
GO
sp_CONFIGURE
GO

You can run following command and check advance global configuration settings.

sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE
GO

(Read More Here)

How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?

One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships. One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships.

Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.

What is an execution plan? When would you use it? How would you view the execution plan?

An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL Server query optimizer for a stored procedure or ad-hoc query and is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure since the plan is the one that SQL Server will place in its cache and use to execute the stored procedure or query. From within Query Analyzer is an option called "Show Execution Plan" (located on the Query drop-down menu). If this option is turned on it will display query execution plan in separate window when query is ran again.



SQL SERVER – Interview Questions and Answers – Part 4



1) General Questions of SQL SERVER
Which command using Query Analyzer will give you the version of SQL server and operating system?

SELECT SERVERPROPERTY ('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

What is SQL Server Agent?

SQL Server agent plays an important role in the day-to-day tasks of a database administrator (DBA). It is often overlooked as one of the main tools for SQL Server management. Its purpose is to ease the implementation of tasks for the DBA, with its full-function scheduling engine, which allows you to schedule your own jobs and scripts. 

Can a stored procedure call itself or recursive stored procedure? How much level SP nesting is possible?

Yes. Because Transact-SQL supports recursion, you can write stored procedures that call themselves. Recursion can be defined as a method of problem solving wherein the solution is arrived at by repetitively applying it to subsets of the problem. A common application of recursive logic is to perform numeric computations that lend themselves to repetitive evaluation by the same processing steps. Stored procedures are nested when one stored procedure calls another or executes managed code by referencing a CLR routine, type, or aggregate. You can nest stored procedures and managed code references up to 32 levels.

What is Log Shipping?

Log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server. Enterprise Editions only supports log shipping. In log shipping the transactional log file from one server is automatically updated into the backup database on the other server. If one server fails, the other server will have the same db and can be used this as the Disaster Recovery plan. The key feature of log shipping is that it will automatically backup transaction logs throughout the day and automatically restore them on the standby server at defined interval.

Name 3 ways to get an accurate count of the number of records in a table?

SELECT * FROM table1

SELECT COUNT(*) FROM table1

SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table1) AND indid < 2

What does it mean to have QUOTED_IDENTIFIER ON? What are the implications of having it OFF?

When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers.

What is the difference between a Local and a Global temporary table?

A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.

A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection is closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.

What is the STUFF function and how does it differ from the REPLACE function?

STUFF function is used to overwrite existing characters. Using this syntax, STUFF (string_expression, start, length, replacement_characters), string_expression is the string that will have characters substituted, start is the starting position, length is the number of characters in the string that are substituted, and replacement_characters are the new characters interjected into the string. REPLACE function to replace existing characters of all occurrences. Using the syntax REPLACE (string_expression, search_string, replacement_string), where every incidence of search_string found in the string_expression will be replaced with replacement_string.

What is PRIMARY KEY?

A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity.

What is UNIQUE KEY constraint?

A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.

What is FOREIGN KEY?

A FOREIGN KEY constraint prevents any actions that would destroy links between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity.

What is CHECK Constraint?

A CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.

What is NOT NULL Constraint?

A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints.


How to get @@ERROR and @@ROWCOUNT at the same time?

If @@Rowcount is checked after Error checking statement then it will have 0 as the value of @@Recordcount as it would have been reset. And if @@Recordcount is checked before the error-checking statement then @@Error would get reset. To get @@error and @@rowcount at the same time do both in same statement and store them in local variable. SELECT @RC = @@ROWCOUNT, @ER = @@ERROR

What is a Scheduled Jobs or What is a Scheduled Tasks?

Scheduled tasks let user automate processes that run on regular or predictable cycles. User can schedule administrative tasks, such as cube processing, to run during times of slow business activity. User can also determine the order in which tasks run by creating job steps within a SQL Server Agent job. E.g. back up database, Update Stats of Tables. Job steps give user control over flow of execution. If one job fails, user can configure SQL Server Agent to continue to run the remaining tasks or to stop execution.

What are the advantages of using Stored Procedures?

  • Stored procedure can reduced network traffic and latency, boosting application performance.
  • Stored procedure execution plans can be reused, staying cached in SQL Server's memory, reducing server overhead.
  • Stored procedures help promote code reuse.
  • Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients.
  • Stored procedures provide better security to your data.

What is a table called, if it has neither Cluster nor Non-cluster Index? What is it used for?

Unindexed table or Heap. Microsoft Press Books and Book on Line (BOL) refers it as Heap. A heap is a table that does not have a clustered index and, therefore, the pages are not linked by pointers. The IAM pages are the only structures that link the pages in a table together. Unindexed tables are good for fast storing of data. Many times it is better to drop all indexes from table and then do bulk of inserts and to restore those indexes after that.

Can SQL Servers linked to other servers like Oracle?

SQL Server can be linked to any server provided it has OLE-DB provider from Microsoft to allow a link. E.g. Oracle has an OLE-DB provider for oracle that Microsoft provides to add it as linked server to SQL Server group

What is BCP? When does it used?

BulkCopy is a tool used to copy huge amount of data from tables and views. BCP does not copy the structures same as source to destination. BULK INSERT command helps to import a data file into a database table or view in a user-specified format.