Monday, July 28, 2008

Oracle connection strings

Oracle ODBC connection strings

Open connection to Oracle database using ODBC
"Driver= {Microsoft ODBCforOracle};Server=Your_Oracle_Server.world;Uid=Your_Username;Pwd=Your_Password;" Oracle OLE DB & OleDbConnection (.NET framework) connection strings Open connection to Oracle database with standard security:
1. "Provider=MSDAORA;Data Source= Your_Oracle_Database;UserId=Your_Username;Password=Your_Password;"
2. "Provider= OraOLEDB.Oracle;Your_Oracle_Database;UserId=Your_Username;Password=Your_Password;"

Open trusted connection to Oracle database
"Provider= OraOLEDB.Oracle;DataSource=Your_Oracle_Database;OSAuthent=1;"

MySQL connection strings

MySQL ODBC connection strings

Open connection to local MySQL database using MySQL ODBC 3.51 Driver
"Provider=MSDASQL; DRIVER={MySQL ODBC 3.51Driver}; SERVER= localhost; DATABASE=Your_MySQL_Database; UID= Your_Username; PASSWORD=Your_Password; OPTION=3"

MySQL OLE DB & OleDbConnection (.NET framework) connection strings

Open connection to MySQL database:
"Provider=MySQLProv;Data Source=Your_MySQL_Database;User Id=Your_Username; Password=Your_Password;"

SQL Server connection strings

SQL ODBC connection strings

Standard Security:<> "Driver={SQLServer};Server=Your_Server_Name;Database=Your_Database_Name;Uid=Your_Username;Pwd=Your_Password;"

Trusted connection:<> "Driver={SQLServer};Server=Your_Server_Name;Database=Your_Database_Name;Trusted_Connection=yes;"

SQL OLE DB connection strings

Standard Security:
"Provider=SQLOLEDB;Data Source=Your_Server_Name;Initial Catalog= Your_Database_Name;UserId=Your_Username;Password=Your_Password;"

Trusted connection:
"Provider=SQLOLEDB;Data Source=Your_Server_Name;Initial Catalog=Your_Database_Name;Integrated Security=SSPI;"

SQL OleDbConnection .NET strings

Standard Security:
"Provider=SQLOLEDB;Data Source=Your_Server_Name;Initial Catalog= Your_Database_Name;UserId=Your_Username;Password=Your_Password;"

Trusted connection:
"Provider=SQLOLEDB;Data Source=Your_Server_Name;Initial Catalog=Your_Database_Name;Integrated Security=SSPI;"

SQL SqlConnection .NET strings

Standard Security:
1. "Data Source=Your_Server_Name;Initial Catalog= Your_Database_Name;UserId=Your_Username;Password=Your_Password;" <>2. "Server=Your_Server_Name;Database=Your_Database_Name;UserID=Your_Username;Password=Your_Password;Trusted_Connection=False"

Trusted connection:
1. "Data Source=Your_Server_Name;Initial Catalog=Your_Database_Name;Integrated Security=SSPI;"
2."Server=Your_Server_Name;Database=Your_Database_Name;Trusted_Connection=True;"

MS Access connection strings

MS Access ODBC connection strings

Standard Security:
"Driver= {MicrosoftAccessDriver(*.mdb)};DBQ=C:\App1\Your_Database_Name.mdb;Uid=Your_Username;Pwd=Your_Password;"

Workgroup:
"Driver={Microsoft Access Driver (*.mdb)}; Dbq=C:\App1\Your_Database_Name.mdb; SystemDB=C:\App1\Your_Database_Name.mdw;"

Exclusive "Driver={Microsoft Access Driver (*.mdb)}; DBQ=C:\App1\Your_Database_Name.mdb; Exclusive=1; Uid=Your_Username; Pwd=Your_Password;"

MS Access OLE DB & OleDbConnection (.NET framework) connection strings

Open connection to Access database:
"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\App1\Your_Database_Name.mdb; User Id=admin; Password="

Open connection to Access database using Workgroup (System database):
"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\App1\Your_Database_Name.mdb; Jet OLEDB:System Database=c:\App1\Your_System_Database_Name.mdw"

Open connection to password protected Access database:
"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\App1\Your_Database_Name.mdb; Jet OLEDB:Database Password=Your_Password"

Open connection to Access database located on a network share:
"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=\\Server_Name\Share_Name\Share_Path\Your_Database_Name.mdb"

Open connection to Access database located on a remote server:
"Provider=MS Remote; Remote Server=http://Your-Remote-Server-IP; Remote Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\App1\Your_Database_Name.mdb"

Thursday, July 10, 2008

[mssql] Performance Tuning SQL Server Joins

One of the best ways to boost JOIN performance is to limit how many rows need to be JOINed. This is especially beneficial for the outer table in a JOIN. Only return absolutely only those rows needed to be JOINed, and no more.

*****

If you perform regular joins between two or more tables in your queries, performance will be optimized if each of the joined columns have their own indexes. This includes adding indexes to the columns in each table used to join the tables. Generally speaking, a clustered key is better than a non-clustered key for optimum JOIN performance.

*****

If you have two or more tables that are frequently joined together, then the columns used for the joins on all tables should have an appropriate index. If the columns used for the joins are not naturally compact, then considering adding surrogate keys to the tables that are compact in order to reduce the size of the keys, thus decreasing read I/O during the join process, increasing overall performance.

*****

JOIN performance has a lot to do with how many rows you can stuff in a data page. For example, let's say you want to JOIN two tables. Most likely, one of these two tables will be smaller than the other, and SQL Server will most likely select the smaller of the two tables to be the inner table of the JOIN. When this happens, SQL Server tries to put the relevant contents of this table into the buffer cache for faster performance. If there is not enough room to put all the relevant data into cache, then SQL Server will have to use additional resources in order to get data into and out of the cache as the JOIN is performed.

If all of the data can be cached, the performance of the JOIN will be faster than if it is not. This comes back to the original statement, that the number of rows in a table can affect JOIN performance. In other words, if a table has no wasted space, it is much more likely to get all of the relevant inner table data into cache, boosting speed. The moral to this story is to try to get as much data stuffed into a data page as possible. This can be done through the use of a high fillfactor, rebuilding indexes often to get rid of empty space, and to optimize datatypes and widths when creating columns in tables.

*****

Keep in mind that when you create foreign keys, an index is not automatically created at the same time. If you ever plan to join a table to the table with the foreign key, using the foreign key as the linking column, then you should consider adding an index to the foreign key column. An index on a foreign key column can substantially boost the performance of many joins.

*****

Avoid joining tables based on columns with few unique values. If columns used for joining aren’t mostly unique, then the SQL Server optimizer may not be able to use an existing index in order to speed up the join. Ideally, for best performance, joins should be done on columns that have unique indexes.

*****

For best join performance, the indexes on the columns being joined should ideally be numeric data types, not CHAR or VARCHAR, or other non-numeric data types. The overhead is lower and join performance is faster.

*****

For maximum performance when joining two or more tables, the indexes on the columns to be joined should have the same data type, and ideally, the same width.

This also means that you shouldn't mix non-Unicode and Unicode datatypes. (e.g. VARCHAR and NVARCHAR). If SQL Server has to implicitly convert the data types to perform the join, this not only slows the joining process, but it also could mean that SQL Server may not use available indexes, performing a table scan instead.

*****

When you create joins using Transact-SQL, you can choose between two different types of syntax: either ANSI or Microsoft. ANSI refers to the ANSI standard for writing joins, and Microsoft refers to the old Microsoft style of writing joins. For example:

ANSI JOIN Syntax

SELECT fname, lname, department
FROM names INNER JOIN departments ON names.employeeid = departments.employeeid

Former Microsoft JOIN Syntax

SELECT fname, lname, department
FROM names, departments
WHERE names.employeeid = departments.employeeid

If written correctly, either format will produce identical results. But that is a big if. The older Microsoft join syntax lends itself to mistakes because the syntax is a little less obvious. On the other hand, the ANSI syntax is very explicit and there is little chance you can make a mistake.

For example, I ran across a slow-performing query from an ERP program. After reviewing the code, which used the Microsoft JOIN syntax, I noticed that instead of creating a LEFT JOIN, the developer had accidentally created a CROSS JOIN instead. In this particular example, less than 10,000 rows should have resulted from the LEFT JOIN, but because a CROSS JOIN was used, over 11 million rows were returned instead. Then the developer used a SELECT DISTINCT to get rid of all the unnecessary rows created by the CROSS JOIN. As you can guess, this made for a very lengthy query. I notified the vendor's support department about it, and they fixed their code.

The moral of this story is that you probably should be using the ANSI syntax, not the old Microsoft syntax. Besides reducing the odds of making silly mistakes, this code is more portable between database, and eventually, I imagine Microsoft will eventually stop supporting the old format, making the ANSI syntax the only option.

[mssql] Data Type Performance Tuning Tips

Always specify the narrowest columns you can. The narrower the column, the less amount of data SQL Server has to store, and the faster SQL Server is able to read and write data. In addition, if any sorts need to be performed on the column, the narrower the column, the faster the sort will be.

*****

If you need to store large strings of data, and they are less than 8,000 characters, use a VARCHAR data type instead of a TEXT data type. TEXT data types have extra overhead that drag down performance.

*****

If you have a database running on SQL Server 7.0, 2000, or 2005 that used to run under version 6.5, and because of the limited row size had to split a column into two or more columns because the column width exceed what SQL Server version 6.5 was able to support, consider altering the table so that the multiple columns now fit back into one column again. This of course assumes that your column width is 8,000 characters or less for ASCII data. This will reduce server overhead and boost performance.

*****

Don't use the NVARCHAR or NCHAR data types unless you need to store 16-bit character (Unicode) data. They take up twice as much space as VARCHAR or CHAR data types, increasing server I/O and wasting unnecessary space in your buffer cache.

*****

If the text data in a column varies greatly in length, use a VARCHAR data type instead of a CHAR data type. The amount of space saved by using VARCHAR over CHAR on variable length columns can greatly reduce I/O reads cache memory used to hold data, improving overall SQL Server performance.

Another advantage of using VARCHAR over CHAR columns is that sorts performed on VARCHAR columns are generally faster than on CHAR columns. This is because the entire width of a CHAR column needs to be sorted.

*****

If a column's data does not vary widely in length, consider using a fixed-length CHAR field instead of a VARCHAR. While it may take up a little more space to store the data, processing fixed-length columns is faster in SQL Server than processing variable-length columns.

*****

Always choose the smallest data type you need to hold the data you need to store in a column. For example, if all you are going to be storing in a column are the numbers 1 through 10, then the TINYINT data type is more appropriate that the INT data type. The same goes for CHAR and VARCHAR data types. Don’t specify more characters in character columns that you need. This allows you to store more rows in your data and index pages, reducing the amount of I/O needed to read them. It also reduces the amount of data moved from the server to the client, reducing network traffic and latency. And last of all, it reduces the amount of wasted space in your buffer cache.

*****

If you have a column that is designed to hold only numbers, use a numeric data type, such as INTEGER, instead of a VARCHAR or CHAR data type. Numeric data types generally require less space to hold the same numeric value as does a character data type. This helps to reduce the size of the columns, and can boost performance when the columns is searched (WHERE clause), joined to another column, or sorted.

*****

Don't use FLOAT or REAL data types for primary keys, as they add unnecessary overhead that hurts performance. Use one of the integer data types instead.

*****

When specifying data types during table creation, always specify NULL or NOT NULL for each column. If you don't, then the column will default to NOT NULL if the ANSI NULL DEFAULT database option is not selected (the default), and will default to NULL of the ANSI NULL DEFAULT database option is selected.

For best performance, and to reduce potential code bugs, columns should ideally be set to NOT NULL. For example, use of the IS NULL keywords in the WHERE clause makes that portion of the query non-sargable, which means that portion of the query cannot use an index.

*****

If you are using fixed length columns (CHAR, NCHAR) in your table, do your best to avoid storing NULLs in them. If you do, the entire amount of space dedicated to the column will be used up. For example, if you have a fixed length column of 255 characters, and if you place a NULL in it, then 255 characters have to be stored in the database. This is a large waste of space that will cause SQL Server to have to perform extra disk I/O to read data pages. It also wastes space in the data cache buffer. Both of these contribute to reduced SQL Server performance.

Instead of using NULLs, use a coding scheme similar to this in your databases:

  • NA: Not applicable
  • NYN: Not yet known
  • TUN: Truly unknown

Such a scheme provides the benefits of using NULLs, but without the drawbacks.

If you really must use NULLs, use a variable length column instead of a fixed length column. Variable length columns only use a very small amount of space to store a NULL.

*****

If you use the CONVERT function to convert a value to a variable length datatype, such as VARCHAR, always specify the length of the variable datatype. If you do not, SQL Server assumes a default length of 30. Ideally, you should specify the shortest length to accomplish the required task. This helps to reduce memory use and SQL Server resources.

[mssql] About Data Type

Exact Numbers

  • bigint Range: -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)
    Space: 8 Bytes

  • int Range: -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)
    Space: 4 Bytes


  • smallint Range: -2^15 (-32,768) to 2^15-1 (32,767)
    Space: 2 Bytes


  • tinyint Range: 0 to 255
    Space: 1 Byte


  • bit Range: 0 (FALSE) or 1 (TRUE)
    Space: 8 bit columns in a table, will be collectively stored as: 1 Byte
    9 - 16 bit columns in a table, will be collectively stored as: 2 Bytes, etc.


  • decimal Declaration: decimal(p[,s]).
    p = Precision - total number of digits stored to both the left and right of the decimal point.
    s = Scale the maximum number of digits stored to the right of the decimal point (optional).

    Precision 1 - 9: Storage is 5 bytes
    Precision 10 - 19: Storage is 9 bytes
    Precision 20 - 28: Storage is 13 bytes
    Precision 29 - 38: Storage is 17 bytes

    Minimum Precision is 1 and Maximum Precision is 38. The Default Precision is 18.
    Note: Decimal is equivalent to Numeric.


  • numeric Declaration: numeric(p[,s]).
    p = Precision - total number of digits stored to both the left and right of the decimal point.
    s = Scale the maximum number of digits stored to the right of the decimal point (optional).

    Precision 1 - 9: Storage is 5 bytes
    Precision 10 - 19: Storage is 9 bytes
    Precision 20 - 28: Storage is 13 bytes
    Precision 29 - 38: Storage is 17 bytes

    Minimum Precision is 1 and Maximum Precision is 38. The Default Precision is 18.
    Note: Numeric is equivalent to Decimal.

  • money Range: -922,337,203,685,477.5808 to 922,337,203,685,477.5807
    Space: 8 bytes


  • smallmoney Range: -214,748.3648 to 214,748.3647
    Space: 4 bytes

Approximate Numbers

  • float Declaration: float(n).
    n = the number of bits used to store the floating point number.

    Range: -1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308

    n Value 1 - 24: Precision - 7 digits: Space - 4 bytes
    n Value 25 - 53: Precision - 15 digits: Space - 8 bytes


  • real Range: -3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38
    Space: 4 bytes

    Note: Real is equivalent to float(24).

Date and Time

  • datetime Range: January 1, 1753, through December 31, 9999
    Accuracy: 3.33 ms Space: 8 bytes (two 4 byte integers). First 4 bytes represent the number of days before or after Jan. 1, 1900. The Second 4 bytes store the time of day as a number 1/3000-second units after 12:00 AM (00:00:00).

  • smalldatetime Range: January 1, 1900, through June 6, 2079
    Accuracy: 1 min Space: 4 bytes (two 2 byte integers). First 2 bytes represent the number of days after Jan. 1, 1900. The Second 2 bytes store the number of minutes after 12:00 AM (00:00:00).

Character Strings

  • char Definition: Fixed-Length character string.
    Declaration: char(n).
    n = the number of characters.

    Space: n number of bytes

    Valid lengths for a char datatype are 1 through 8,000.


  • varchar Definition: Variable-Length character string.
    Declaration: varchar(n | max).
    n = the number of characters.

    Space: characters actually used in datatype (1 byte per character) + 2 additional bytes

    Valid lengths for a varchar datatype are 1 through 8,000. Alternatively, MAX allows for a much larger maximum storage size (2^31-1 bytes - 2,147,483,647 characters).
  • text Definition: Variable-Length character string in the code page of the server.

    Maximum length is 2,147,483,647 characters.
    Note: will be removed in future versions. Use varchar(max) instead.

Unicode Character Strings

  • nchar Definition: Fixed-Length Unicode character string.
    Declaration: nchar(n).
    n = the number of characters.

    Space: n * 2 number of bytes

    Valid lengths for a char datatype are 1 through 4,000.

  • nvarchar Definition: Variable-Length Unicode character string.
    Declaration: nvarchar(n | max).
    n = the number of characters.

    Space: characters actually used in datatype (2 bytes per character) + 2 additional bytes

    Valid lengths for a varchar datatype are 1 through 4,000. Alternatively MAX indicates that the maximum storage size is much larger (2^31-1 bytes - 2,147,483,647 characters).


  • ntext Definition: Variable-Length character string in the code page of the server.

    Maximum length is 1,073,741,823 characters.
    Note: will be removed in future versions. Use nvarchar(max) instead.

Binary Strings

  • binary Definition: Fixed-Length binary data.
    Declaration: binary(n).

    Space: n number of bytes.
    Maximum length is 8000 bytes.


  • varbinary Definition: Variable-Length binary data.
    Declaration: varbinary(n | max).
    n = the number of characters.

    Space: actual number of bytes stored in datatype + 2 additional bytes

    Valid lengths for a varbinary datatype are 1 through 8,000. Alternatively, MAX allows for a much larger maximum storage size (2^31-1 bytes - 2,147,483,647 bytes).


  • image Definition: Variable-Length binary data.

    Maximum length is 2,147,483,647 bytes.

    Note: will be removed in future versions. Use varbinary(max) instead.

[mssql] Datetime Data Type

SQL Server's datetime data type generates a lot of questions and confusion in the SQL Server community. Unlike some other major database platforms, SQL Server doesn't provide discrete date and time data types. Instead, SQL Server's datetime data type does the work of both. Here are answers to six commonly asked questions about how to use SQL Server's datetime data type.

6. How does SQL Server store the datetime data type?

SQL Server uses 8 bytes to store the datetime data type. The first 4 bytes make up an integer value that represents the number of days since January 1, 1900. The second 4 bytes are an integer value that represents the number of milliseconds since midnight.

5. How do I retrieve rows based on datetime values?

SQL Server recognizes date and time data enclosed in single quotes. You can couple date and time values together or use them independently. You can also combine character date formats ('May 15, 2004 4 am'), numeric date formats ('5/15/2004 04:30'), or contiguous string formats ('20040515') with standard <, >, or = operators, as the following example shows:

SELECT * FROM orders WHERE OrderDate < 'May 15, 2004'

4. How do I retrieve only the date or time portion of the data?

You can use T-SQL's DATEPART() function to return a subset of the values that SQL Server's datetime columns store. The DATEPART() function uses two arguments. The first argument specifies the portion of the date that you want, and the second value specifies the datetime column:

SELECT orderID, DATEPART(MM,OrderDate) AS OrderMonth FROM Orders

3. How do I insert a value into a datetime column?

To insert values into a datetime column, you need to enclose the values in single quotes, then use one of SQL Server's date formats to supply the date value that you want to insert. For example:

DECLARE @MyTable TABLE
(MyDateTime DATETIME)
INSERT INTO @MyTable VALUES ('May 15, 2004 11:25am')

2. How do I find the day of the week?

Using the weekday argument as its first parameter, SQL Server's DATEPART() function returns the day of the week, returning 1 for Sunday, 2 for Monday, and so on. The following example uses the GETDATE() function combined with the DATEPART() function to retrieve the current day value:

SELECT DATEPART(weekday, GETDATE())

1. How can I find the last day of the month?

You can combine T-SQL's DATEADD() and DATEDIFF() functions to calculate different date and time values. Subtract 5ms from the first day of the next month to find the last day of the current month:

SELECT DATEADD(ms,-5,DATEADD(mm, DATEDIFF(m,0,GETDATE()  )+1, 0))