Systems Engineering and RDBMS

Archive for April, 2007

Case Sensitive Searches in SQL Server

Posted by decipherinfosys on April 30, 2007

Some time ago, we had blogged about doing case INsensitive searches in Oracle – you can read more on that post here. Oracle, by default is case sensitive whereas SQL Server installations by default are case-insensitive i.e. if you have a column called say FIRST_NAME and you want it to be unique, you can have SAM and sam as two values in it in Oracle where as in SQL Server, that will error out with a unique key violation.

There are of course, ways to change that behavior. We had covered the Oracle bit in the blog post mentioned above and in this one, we will see how you can achieve the same thing in SQL Server. In SQL Server, you can specify collation at the Instance, Database, table, column or the query level. One can choose from Windows collations or SQL Server collations. Windows collations are collations that are defined for SQL Server installations to support Windows locales and SQL Server collations are collations to match the code-page number and sort order combinations.

Suppose, that you do not have the option of making collation changes at the instance, database or column level. How can you make a query collation change to make the search case sensitive? Here is an example that illustrates that feature:

First, let’s see what is the instance level collation setting:

select serverproperty(‘collation’), serverproperty(‘sqlsortorder’)

—————————– —
SQL_Latin1_General_CP1_CI_AS 52

As you can see from above, this is a Case-Insensitive collation at the server level. Now, let’s do some searches based on some dummy data:

set nocount on

declare @table_demo table
(col1 varchar(20) )
insert into @table_demo values (‘SAM’)
insert into @table_demo values (‘sam’)
insert into @table_demo values (‘sam king’)
insert into @table_demo values (‘Sam King’)
insert into @table_demo values (‘SAm KINg’)

/*Normal case insensitive search*/
select * from @table_demo where col1 like ‘sam%’

sam king
Sam King

/*Case Sensitive search*/
select * from @table_demo where col1 like ‘sam%’ collate SQL_Latin1_General_CP1_CS_AS

sam king

As you can see from above, the case-sensitive search returns back only the two records with the matching case where-as the other one returns back all the records regardless of the case of the text. Typically, if you need this kind of behavior, you should account for it at the time of designing your applications – however, as is the very nature of growth, you might not have anticipated this at design time – an example is an install that is done in say Western European or Asian countries and the tempdb collation is thus the collation of the server whereas your user database collation is different that that server collation – if you have queries that are using temporary tables and doing joins with the data set coming from the user database tables, there will be collation mis-matches, the sort rules will be different – that is one place, you can take advantage of the fact that you can play with collation at the query level as well.

Posted in SQL Server | Leave a Comment »

RDBMS Market Share

Posted by decipherinfosys on April 29, 2007

ComputerWorld had an interesting article on the market share and the growth experienced by Oracle, IBM, Microsoft, Sybase and NCR. Oracle still leads the charts – you can read more on that report in their article. It also mentions the marketing pact between IBM and MySQL to bring support for MySQL to the IBM system i business computing platform. MySQL is the world’s most popular open source database software and is fast catching up on the RDBMS market in the SMB market. You can read more about this partnership announcement at the MySQL site as well as the eweek article.

Posted in News, Technology | Leave a Comment »

Writing data to a text file from Oracle

Posted by decipherinfosys on April 28, 2007

In a previous blog post, we saw how using an external table, we can read the data in the text file and put it in the respective table(s) in the schema. In a similar fashion, we can also read data from regular tables and write contents of the table into flat file. Only thing to remember is, to write a data into text file, we need to use ORACLE_DATAPUMP access driver.

Up until Oracle 9i, external tables were read only. It means we could not write into external table from the schema. To load data from flat file Oracle uses ORACLE_LOADER access driver which is based on the SQL*Loader API. Starting Oracle 10g, Oracle introduced another method to export/import the data, called data pump (export/import on steroid as they call it). Using same data pump access driver, now we can also write into text/external file from database. For unloading data in prior versions, look at the different options that are presented by Oracle Guru Tom Kyte on his site.

To deal with external tables, we need to create Oracle directory object which maps to folder on the operating system. So, we will create directory object which specifies an alias for a folder/directory on a server. Make sure that you have the correct privilege to create the directory object and correct permission on the o/s folder so that there is no error when we write data into text file. Again, the directory structure we use is defined by user_dump_dest oracle parameter. We can check the value of this parameter using SHOW PARAMETER command. Following is the command to create directory. Path could be different on your system so modify the command before executing it. Let us first create directory object from SQL*Plus.

SQL> CREATE OR REPLACE DIRECTORY DATALOAD AS ‘D:\oracle\product\10.2.0\admin\orcl\udump’;

Directory created.

As mentioned in other blog post, Oracle user, who issues the create directory command, automatically gets read and write privilege on the directory. Other oracle users should be given specific grants to read and write content from the directory.

Now we will create an empty table and populate it with some data. Connect to the user using SQL*Plus and execute following sqls.


VALUES(3,’JOHNS CREEK’,’GA’,’30097′,’USA’)

This time we will create external tables to write data directly into file from the database. Execute following statement from SQL> prompt.

CREATE TABLE external_city
TYPE oracle_datapump
LOCATION (‘external_city.txt’)

Above command will write to an external table i.e. Oracle will push data to the file from the database upon execution of the command. The query (select statement) is shown in simplest form. We can write complex select statement which involves joins, functions etc. as well to create external table. One key thing to note is, access driver (TYPE) is defined as oracle_datapump. Also we haven’t specified data type for any column in the CREATE TABLE clause. Oracle will automatically take the data type of columns mentioned in the SELECT clause. We can check it by describing the table.

SQL> desc external_city
Name Null? Type
——————— ——– ————

We can even create table without specifying any column names. Run following statement from SQL> prompt. If you already have created table earlier, drop the table and delete the file from the o/s folder before executing this statement.

CREATE TABLE external_city
TYPE oracle_datapump
LOCATION (‘external_city.txt’)

Whenever table is created, Oracle always creates at least two files. Text file as specified in LOCATION clause and log file. Every time when SELECT is performed, log file will be updated. Some points to keep in mind about the file are
• Dropping a table will not drop the file. One has to delete it once table is dropped.
• File can be dropped without dropping a table, but later when SELECT is performed, oracle will give an error.
• File cannot be edited manually using any other editor like notepad, wordpad or vi etc.
• Multiple files can be specified in LOCATION clause but it has to follow directory:filename syntax.

Once table is created, we can use SELECT statement against external table in a same way as we use it against regular tables. Table can be used in join condition with other tables as well.

SQL> SELECT ID, City, State, Zip, Cntry FROM External_City;

———- ————— — ———- ———-

Posted in Oracle | 1 Comment »

Windows Server Longhorn Beta 3 released

Posted by decipherinfosys on April 27, 2007

Beta 3 for Longhorn is now available for download here. You can get the technical information on Longhorn from Microsoft’s Technical Library on this subject – here.

Posted in News, Technology | Leave a Comment »

Putting cost limits on bad Queries

Posted by decipherinfosys on April 27, 2007

SQL Server has a query governor cost option that can be applied to throttle bad/runaway queries in the application.  This option (QUERY_GOVERNOR_COST_LIMIT) is present in SQL Server and can be changed either at the server level or at the connection level.  If you are going to change it at the server level, you have to use the sp_configure command and if you want to change it at the connection level, you can use the SET command for changing the setting for that connection only.  What this option enables does is that it applies a limit (in seconds) to the time duration for which a query can run.  Once this setting is in place, the query engine will not allow the execution of any queries that have an “estimated” (not runtime) cost (in seconds) that exceeds that value.  This setting takes place at execute or run time and not at the parse time.

So, where can one use this option?  We had a client application which required us to build an interface for doing adhoc reporting for end users.  If we expose the application to the non-technical end users via an interface and let them create the adhoc reports to run and if they make bad choices which results into bad SQL queries being formed, then those SQLs can put a lot of load on the system.  Guaranteed that we controlled the mapping and would not have allowed a lot of bad SQL formation through the code generator but there is still always a corner case.  So, at the time of making the connection, we set this setting to be 5 seconds and any query that exceeded 5 seconds would then run into an error.  If there is a particular query that goes above the threshold, then an error is raised and that error gets trapped and the user gets notified – they also get notified of potential modifications that they can make in order to make it work better.

This option can also help in benchmark efforts to weed out bad query code – though the same can be done by profiling the code and then later on querying atop the results to see which ones need to be fixed.  Having it at the connection level helps though since it does not put any load on the system and we can easily trap and log the different bad SQL code.

Posted in SQL Server | 3 Comments »

Determining when statistics were last collected

Posted by decipherinfosys on April 26, 2007


Oracle has last_analyzed column in user_tables and user_indexes table which is accessible to schema owner. One can check for both tables and indexes when statistics was last collected.

SQL> SELECT table_name, last_analyzed
2 FROM user_tables
3 WHERE temporary = ‘N’
4 /

—————————— ———

Similarly, we can also check when statistics was collected for indexes.  You can order the results whichever way you want – by last collected date or by table/index.

SQL> SELECT ui.index_name, ui.last_analyzed
2 FROM user_indexes ui
3 INNER JOIN user_tables ut ON ut.table_name = ui.table_name
4 WHERE ut.temporary = ‘N’
5 /

—————————— ———

MS SQLServer:

In SQLServer one can use nondeterministic system function STATS_DATE to see when was the last time statistics was updated for specific index.

STATS_DATE function takes two arguments table_id and index_id. Following is the SQL followed by output. You can run it from query analyzer or management studio.

SELECT t.table_name, as Index_Name,
STATS_DATE(, i.indid) as Statistics_last_collected
FROM information_Schema.tables t
INNER JOIN sysindexes i ON object_id(t.table_name) =
WHERE t.table_type = ‘BASE TABLE’
AND i.Name is NOT NULL
AND i.Name not like ‘_WA_Sys%’

Table_Name Index_Name Last_Stat_Date
———- ————- ———————–
TEST_GROUP PK_TEST_GROUP 2007-04-25 12:29:17.150
TEST_USER PK_TEST_USER 2007-04-25 12:29:17.150

Posted in Oracle, SQL Server | Leave a Comment »

First Public Beta of Orcas and .Net 3.5

Posted by decipherinfosys on April 25, 2007

The first beta of MSFT’s upcoming Visual Studio product and interim framework was released last week. This next release of Visual Studio is code-named Orcas. This upcoming release of Visual Studio is designed to provide a development environment for Microsoft’s new and upcoming platform advances, such as those in the Windows Vista operating system, 2007 Office System, SQL Server 2005 and Windows “Longhorn” server. It also adds a wide breadth of tooling for everything from back-end data connectivity and unit testing to Web development and UI design.

Beta 1 will not offer tooling for Silverlight, the recently branded cross-browser plug-in technology, formerly known as Windows Presentation Foundation/Everywhere (WPF/E). You can download the CTP (Community Technology Preview) from the MSFT download site.

Posted in News, Technology | Leave a Comment »

Google future apps?

Posted by decipherinfosys on April 24, 2007

A couple of days ago we had mentioned about Google’s upcoming summer launch of a Power-point type presentation application. Web Worker daily had an interesting post on eight google applications for the future – there is also a link included for the google wish list discussion which is an interesting list.

A lot of these new offerings are via the acquisitions that Google has made in the recent times but for the end users as well companies the good news is the integration between all these applications.

Posted in News, Technology | Leave a Comment »

Installing 10gR2 Client

Posted by decipherinfosys on April 24, 2007

Here are the step by step instructions:

1) Copy and unzip(if necessary) the 10gR2 client installation package.
2) Start the installation by executing either in the “client” directory:
a. setup.exe (this is for windows)
b. ./runInstaller (this is for unix)
3) Click Next


4) (Unix Only) insure the oraInventory directory is in the $ORACLE_BASE path. The group should be oinstall. Click Next.


5) Choose the Administrator option if you want to install in the Administrator mode or choose from runtime or custom if you want to pick from the individual components to install. Click Next.


6) Choose and appropriate name for the Oracle home. The one below can be used. Ensure the path specified is the correct oracle home directory. This is where the software will be installed. Click Next.


7) The installation will check prerequisites for the machine. If all requirements succeed, then Click Next. If some do not succeed, then you will need to fix the issues and start the installation over.


8 ) Ensure the Installation Type says “Administrator” Make sure the Space Requirements are met. Click Install.


9) The installation will continue with a progress bar.


10) A popup window will appear. Check “Perform typical configuration” then click Next.


11) Click Next.


12) Click Finish.


13) (UNIX only) You will need to run the scripts specified as root in order to continue with the installation. Only after they have been run, Click OK.


14) Click Exit. The installation is complete.


15) Get your tnsnames.ora file if you maintain a common file in some place and place it into the <ORACLE_HOME>/network/admin directory on the machine you just installed the oracle client. For UNIX, be sure you do this as Oracle. Test your connections using SQL plus.

Posted in Oracle | 1 Comment »

LIKE comparisons in SQL Server

Posted by decipherinfosys on April 23, 2007

LIKE operator is typically used for wild-card pattern matching.  However, all pattern matching with a LIKE operator does not always return in a SARGable (Searchable Argument) expression which means that an index may or may not be used when using a LIKE operator.

col1 LIKE ‘abc%’

will make use of an index since the wild card search is done after specifying the initial characters, however

col1 like ‘%abc%’

will not make use of a seek operation on the index.

Majority of the wildcard character searches are clearly defined in BOL – i.e. using %, _ ,[], [^] (you can look up BOL for “LIKE comparisons” and all this is detailed)…let’s cover an example that details pattern matching using the escape clause.  If the string that you want to do pattern matching on, also includes one or more of the special wildcard characters, then you need to make use of the escape clause – example:

declare @table table (col1 varchar(10))
insert into @table values (‘John Doe’)
insert into @table values (‘John%’)
insert into @table values (‘John%Doe’)

select * from @table where col1 like ‘John%’

John Doe

However, if you want to match only the entries that have the wildcard % in them, then you can use the escape clause – example:

declare @table table (col1 varchar(10))
insert into @table values (‘John Doe’)
insert into @table values (‘John%’)
insert into @table values (‘John%Doe’)

select * from @table where col1 like ‘John!%%’ escape ‘!’


The escape character could be any character as long as that is not the character that you want to include in the pattern matching…example, this will return the same results:

select * from @table where col1 like ‘John+%%’ escape ‘+’

Posted in SQL Server | Leave a Comment »