====== Data Warehousing: SQL Server Specifics (Warehousing) ====== ====== ====== ==== Created at 17 OCT 1997 04:09PM ==== These are several suggestions for setting up data warehousing from LH to SQL Server.   **Warehousing Defaults** ** ** These are the suggestions for the default data type mappings.   |LH Type |DataSet Type |SQL Server Type| |BOOLEAN |BIT |tinyint | |CHAR |CHAR(254) |char(254) | |DATE |DATETIME(19) |datetime | |DATETIME |DATETIME(19) |datetime | |DECIMAL |DECIMAL(19,4) |money | |DOLLARS |DECIMAL(19,4) |money | |FLOAT |DOUBLE |float | |INTEGER |SINT4 |int | |TEXT |VARCHAR(254) |varchar(254) | |TIME |DATETIME(19) |datetime | |VARBINARY|VARBINARY(255)|varbinary(255) | |VARCHAR |VARCHAR(254) |varchar(254) |   For the CHAR type, adjust the DataSet and SQL Server type to the maximum length expected.  Use the //Details// button in the //Create Warehouse Procedure// wizard to check the maximum length or other details (like the description) of the LH field.   For the LH DECIMAL type, if more than 4 decimal digits of precision are required for a specific field, change the corresponding SQL Server type to float and the DataSet type to DOUBLE.   For the LH TEXT and VARCHAR types, if a field is expected to contain more than 254 characters, change the corresponding SQL Server type to text and the DataSet type to LONGVARCHAR(65531).  Similarly for the LH VARBINARY type, if it will exceed 255 bytes, change the corresponding SQL Server type to image and the DataSet type to LONGVARBINARY(65531).   The SQL Server type //tinyint// is used instead of //bit// because the //bit// type cannot contain nulls and cannot be indexed.  Likewise, avoid the //text// and//image// SQL Server types, because certain Transact-SQL features cannot be used with these types and these data types use relatively large amounts of disk space due to a 2k block allocation algorithm.     **Create Table script** ** ** Recent versions of SQL Server support the //primary key// clause in the create table statement, however it is suggested that you use the//sp_primarykey// stored procedure to declare the primary key so that your scripts will work on SQL Server from version 1.1 through today.   From the purchase order example, the create table scripts generated by the //Create Warehouse Procedure// wizard are as follows:   create table purchase_orders        (        po_no   int          not null primary key,        po_date datetime         null,        po_desc varchar(254)     null        )   create table purchase_order_lines        (        po_no     int   not null primary key,        po_line   int   not null primary key,        stk_no    int       null,        qty       int       null,        unit_cost money     null,        qty_rcvd  int       null        )   In each create script, remove the //primary key// clause and add a call to the sp_primarykey stored procedure after each //create//// table// statement:   create table purchase_orders        (        po_no   int          not null,        po_date datetime         null,        po_desc varchar(254)     null        ) exec sp_primarykey 'purchase_orders', 'po_no'   create table purchase_order_lines        (        po_no     int   not null,        po_line   int   not null,        stk_no    int       null,        qty       int       null,        unit_cost money     null,        qty_rcvd  int       null        ) exec sp_primarykey 'purchase_order_lines', 'po_no', 'po_line'   This uses one of the handiest features of Transact-SQL:  the ability to put multiple Transact-SQL statements into a single executable script.  There are some exceptions to this feature; for example, you cannot combine any other Transact-SQL statements with a //create procedure// statement.     **Clear Table script** ** ** The syntax  delete * from   is not valid in Transact-SQL.  Instead, use  delete from
  (sans asterisk).  Alternatively, the Transact-SQL statement  truncate table
  is the fastest way to delete all rows in a table, but does not use transaction logging.     **Having it Both Ways** ** ** If your Warehouse Procedures need to be able to conditionally switch between SQL Server-specific scripts and generic ANSI-SQL scripts, use the Connection Object properties XO_SOURCENAME$ and XO_SOURCETYPE$ to determine what connection definition and type (ODBC, SQLServer,Oracle) are being used:   QryGetProperty(Handle, QRY_CONNECTION$, hXO) XOGetProperty(hXO, XO_SOURCETYPE$, Type) begin case   case Type _eqc "SQLServer"     * SQL Server specific functionality goes here   case Type _eqc "Oracle"     * Oracle specific functionality goes here   case OTHERWISE$     * generic functionality goes here end case     **Using Stored Procedures** ** ** Just about anything you can do in a Transact-SQL script can be done in a Transact-SQL stored procedure.  There are several reasons to use stored procedures:   1.         Stored procedures are pre-compiled, so an execution plan already exists and is often cached if the procedure is being called repeatedly. 2.         Security is simple:  Instead of granting rights on the table, rights are granted on the stored procedure.  This way, no one needs rights to modify the table. 3.         The Transact-SQL logic is in the stored procedure itself, so the scripts that have to be repeatedly sent over the network are used only to execute that procedure and are therefore as tiny as possible.   Using the purchase order example insert script:   insert into purchase_orders        (        po_no, po_date, po_desc        ) values        (        :po_no, :po_date, :po_desc        )   This script creates a stored procedure which handles the insert:   create procedure insert_po        (        @po_no   int,        @po_date datetime,        @po_desc varchar(254)        ) as        insert into purchase_orders               (               po_no, po_date, po_desc               )        values               (               @po_no, @po_date, @po_desc               )   Replace the automatically generated insert script with the following script, which executes the stored procedure to insert one row:   exec insert_po :po_no, :po_date, :po_desc