Friday, January 30, 2009

SET Command in DOS

SET

Windows NT 4/Windows 2000 Syntax

Displays, sets, or removes cmd.exe environment variables.

SET [variable=[string]]

variable
Specifies the environment-variable name.
string
Specifies a series of characters to assign to the variable.

Type SET without parameters to display the current environment variables.

If Command Extensions are enabled SET changes as follows:

SET command invoked with just a variable name, no equal sign or value will display the value of all variables whose prefix matches the name given to the SET command. For example:

    SET P

would display all variables that begin with the letter 'P'

SET command will set the ERRORLEVEL to 1 if the variable name is not found in the current environment.

SET command will not allow an equal sign (=) to be part of the name of a variable.
However, SET command will allow an equal sign in the value of an environment variable in any position other than the first character.

One new switch has been added to the SET command in Windows NT 4, and another one in Windows 2000:

    SET /A expression     SET /P variable=[promptString] 

The /A switch specifies that the string to the right of the equal sign is a numerical expression that is evaluated. The expression evaluator is pretty simple and supports the following operations, in decreasing order of precedence:

()
grouping
* / %
arithmetic operators
+ -
arithmetic operators
<< >>
logical shift
&
bitwise and
ˆ
bitwise exclusive or
|
bitwise or
= *= /= %= += -= &= ˆ= |= <<= >>=
assignment
,
expression separator
Warning note:A note on NT 4's SET /A switch from Walter Zackery in a message on alt.msdos.batch.nt:
 "The SET /A command has a long list of problems. I wouldn't use it for much more than simple arithmetic, although even then it truncates all answers to integers."

 

On the other hand, limited though it may seem, the SET command's math function can even be used for a complex task like calculating the date of Easter Day for any year.

If you use any of the logical or modulus operators, you will need to enclose the expression string in quotes. Any non-numeric strings in the expression are treated as environment variable names whose values are converted to numbers before using them. If an environment variable name is specified but is not defined in the current environment, then a value of zero is used. This allows you to do arithmetic with environment variable values without having to type all those % signs to get their values. If SET /A is executed from the command line outside of a command script, then it displays the final value of the expression. The assignment operator requires an environment variable name to the left of the assignment operator. Numeric values are decimal numbers, unless prefixed by 0x for hexidecimal numbers, 0b for binary numbers and 0 for octals numbers. So 0x12 is the same as 0b10010 is the same as 022. Please note that the octal notation can be confusing: 08 and 09 are not valid numbers because 8 and 9 are not valid octal digits.

The /P switch allows you to set the value of a variable to a line of input entered by the user. Displays the specified promptString before reading the line of input. The promptString can be empty.

Environment variable substitution has been enhanced as follows:

    %PATH:str1=str2%

would expand the PATH environment variable, substituting each occurrence of "str1" in the expanded result with "str2". "str2" can be the empty string to effectively delete all occurrences of "str1" from the expanded output. "str1" can begin with an asterisk, in which case it will match everything from the begining of the expanded output to the first occurrence of the remaining portion of str1.

May also specify substrings for an expansion.

    %PATH:~10,5%

would expand the PATH environment variable, and then use only the 5 characters that begin at the 11th (offset 10) character of the expanded result.
If the length is not specified, then it defaults to the remainder of the variable value.
If either number (offset or length) is negative, then the number used is the length of the environment variable value added to the offset or length specified.

    %PATH:~-10%

would extract the last 10 characters of the PATH variable.

    %PATH:~0,-2%

would extract all but the last 2 characters of the PATH variable.

Finally, support for delayed environment variable expansion has been added. This support is always disabled by default, but may be enabled/disabled via the /V command line switch to CMD.EXE. See CMD /?

Delayed environment variable expansion is useful for getting around the limitations of the current expansion which happens when a line of text is read, not when it is executed.
The following example demonstrates the problem with immediate variable expansion:

    set VAR=before     if "%VAR%" == "before" (         set VAR=after;         if "%VAR%" == "after" @echo If you see this, it worked     ) 

would never display the message, since the %VAR% in BOTH IF statements is substituted when the first IF statement is read, since it logically includes the body of the IF, which is a compound statement.
So the IF inside the compound statement is really comparing "before" with "after" which will never be equal.
Similarly, the following example will not work as expected:

    set LIST=     for %i in (*) do set LIST=%LIST% %i     echo %LIST% 

in that it will NOT build up a list of files in the current directory, but instead will just set the LIST variable to the last file found.
Again, this is because the %LIST% is expanded just once when the FOR statement is read, and at that time the LIST variable is empty.
So the actual FOR loop we are executing is:

    for %i in (*) do set LIST= %i

which just keeps setting LIST to the last file found.

Delayed environment variable expansion allows you to use a different character (the exclamation mark) to expand environment variables at execution time.
If delayed variable expansion is enabled, the above examples could be written as follows to work as intended:

    set VAR=before     if "%VAR%" == "before" (         set VAR=after         if "!VAR!" == "after" @echo If you see this, it worked     )      set LIST=     for %i in (*) do set LIST=!LIST! %i     echo %LIST% 

If Command Extensions are enabled, then there are several dynamic environment variables that can be expanded but which don't show up in the list of variables displayed by SET.
These variable values are computed dynamically each time the value of the variable is expanded.
If the user explicitly defines a variable with one of these names, then that definition will override the dynamic one described below:

%CD%
expands to the current directory string
%DATE%
expands to current date using same format as DATE command
%TIME%
expands to current time using same format as TIME command
%RANDOM%
expands to a random decimal number between 0 and 32767
%ERRORLEVEL%
expands to the current ERRORLEVEL value
%CMDEXTVERSION%
expands to the current Command Processor Extensions version number
%CMDCMDLINE%
expands to the original command line that invoked the Command Processor

Thursday, January 29, 2009

SQL Server 2008 and Data Compression


Thank you to Nicholas for this artical on SQLServerCentral.com
One of the large advantages of migrating up to SQL Server 2008 is the ability to compress your data, reducing your disk overhead. It is a relatively simple process to compress your data.
Unfortunately from a production standpoint it is only available on the SQL Server 2008 Enterprise Edition (as per http://msdn.microsoft.com/en-us/library/cc645993.aspx), it can however also be used on the Developer Edition (http://www.microsoft.com/sqlserver/2008/en/us/developer.aspx)
The reduction in I/O required to pull data can lead to significant performance improvements, particularly in data warehouse and data mart environments where you are working with extremely large datasets.

What is Data Compression

Data compression is exactly what is says it is. It is a way of compressing the data within your database so that you can reduce greatly the amount of storage space required to host the data. There is a caveat with this, depending upon the amount of stored data within a table, the allocation unit size of your disk and the datatypes you could in fact end up using MORE storage
Note: Allocation Unit Size (AUS) is also known as the cluster or blocksize of your disk. This size is set when you format your disk and can range in size from 512 Bytes to 64 KB. The default AUS is based upon the size of the disk, seehttp://support.microsoft.com/kb/140365 for more information. Larger file allocations will provide performance improvements for applications such as SQL Server, particularly the 64 KB AUS. The size of a SQL Server extent (8 pages) is 64 KB, and so optimizes performance. The downside of a larger AUS is that is takes a great deal more space to hold a file on the disk, so if you have a lot of smaller files you could end up using a far more disk space than you need to as the disk has to allocate 64 KB of space for even a 2 KB file. If you are curious about the existing AUS on your partitions Microsoft have published a technet article with a script to show the partition propertieshttp://www.microsoft.com/technet/scriptcenter/guide/sas_fsd_grsi.mspx?mfr=true
There are two types of data compression:
  • Row compression
  • Page compression
Row level compression will provide savings by not storing blank characters within fixed character strings (such as a char(10) with a 5 character value). Null and 0 values are not stored and so do not incur additional storage overhead. For a list of data types that support row level compression see http://msdn.microsoft.com/en-us/library/cc280576.aspx
Page compression uses a far more complex algorithm to minimize the storage space of data, known as dictionary compression.  SQL Server looks at all of the data stored on a page and builds a dictionary based upon that data which can be referenced for repeated values, and only the dictionary id and changes of the dictionary value are stored. This provides great savings for similar patterned data. For full details on page level compression and how it works visithttp://msdn.microsoft.com/en-us/library/cc280464.aspx  Page compression includesrow compression, so you get the benefit of both.

Potential Issues with Data Compression

Data compression is not for everybody.  Depending upon the workload of your system, the performance requirements, and whether or not you use encryption this might not be the thing for you. There is a CPU overhead associated with using data compression, and this may adversely impact your system. High volume OLTP systems could be significantly impacted by attempting to implement data compression.

How Do I know if Data Compression is Right for Me?

  • Estimate the potential storage savings that you could get by implementing  (you could actually end up using MORE storage within certain circumstances)
  • Complete a baseline performance analysis of your database server and reproduce this in a development or staging environment.  Enable compression and evaluate the performance against that baseline.
  • Look at which tables can provide you the biggest benefit. I have found that an audit table I have will actually use 75% savings in disk space with no impact to the application on top of the database. Tables which have a lot of repetitive or numerical data or CHAR columns that are not fully populated are usually excellent candidates for compression.
  • Check and recheck your analysis against the baseline, and seek feedback from the users if you do implement it in a production environment.

How Do I know if a Table is Worth Compressing?

There is a stored procedure in SQL Server 2008 called sp_estimate_data_compression_savings. This procedure accepts 5 parameters.
1.       @schema_name
2.       @object_name
3.       @index_id
4.       @partition_number
5.       @data_compression
The two critical parameters are @object_name and @data_compression.
The object name refers to the table that you wish to evaluate and @data_compression can have one of three values (NONE, ROW, PAGE). Depending upon the value passed this will perform estimations for the two compression types and strangely for no compression.
The additional parameters provide some more advanced estimation options. @schema_name allows you to estimate the savings against tables on a different schema (by default the procedure only looks at tables within your own schema). @index_id will allow you to specify the estimated savings for a single index on a table based upon the index id, left with the default value of NULL it will assess all of the indexes on that table. @partition_number will allow you to define particular partitions on a table to evaluate potential savings. This can be very useful for estimating the savings on older data, rarely changed living on a partition, which you may want to compress as opposed to more volatile data within a different partition which may not be a good candidate for compression. Worth noting, you have to define an index id in order to evaluate a partition.

Estimating Savings for Entire Databases

It is obviously a time consuming task to go through each and every table an get an estimation of the potential savings you could find, which I why I created a procedure USP_Compression_Savings_By_DB (atttached to this article), which will go out and provide the estimated compression savings for all tables within a single database, or within all databases and load that information into a table for later analysis.
The procedure can be created in the database of your choice and the final analysis table located in another database of your choice. It accepts two parameters, both of which are optional.
  • @checkdb – this is the name of the database that we will be checking, if left null then all databases will be checked and the savings estimated
  • @admindbname – should you wish to put the results into a particular database you can put a value here, by default it will use the database in which the procedure resides
PLEASE NOTE: The sp_estimate_data_compression_savings procedure is a bit of a heavy hitter, and obviously with checking all tables in a database (or on an instance) using USP_Compression_Savings_By_DB, your system will be hit all the harder. I would recommend running this against a recent dump of your production databases restored on to another system. At the very least run it during the period of least activity on your system.

Final Thoughts

Compression can be a very useful tool and can provide huge benefits in not only storage but performance in heavy read applications.
Ensure to perform all of the analysis required to prevent your production systems from being negatively impacted by enabling data compression.
The code for usp_compression_savings_by_db is included in the Resource section below.

Resources:

usp_compression_savings_by_db.sql

By Nicholas Cain, 2009/01/07

Reference :- http://www.sqlservercentral.com/articles/SQL+Server+2008/65292/

Friday, January 16, 2009

Simple way to determine the number of days in a month for any given date


In our regular development process we sometime require the number of days in a particular month. What will you do for this? There are several methods to get this. The one method is given below:-
DECLARE @varDate datetime
DECLARE @varMonthDate datetime
DECLARE @varYear datetime
DECLARE @varFOM varchar(10)
DECLARE @NumDaysInMonth as int
select @varDate = '2/1/2009'
select @varMonthDate = MONTH(@varDate)
select @varYear = YEAR(@varDate)
select @varFOM = CAST(CAST(@varMonthDate as int) as varchar) + '/1/' +
CAST(CAST(@varYear as int) as varchar)
select @NumDaysInMonth = DATEDIFF (day,@varFOM,DATEADD(mm,1,@varFOM))
select @NumDaysInMonth as DaysInMonth

I think this is very awkward method to get days of a month. We wrote many lines to get a particular result. I am thankful to my colleague Amit Pawar that discovered a simplest query to get days in a particular month. The query he wrote is as follows:-
Select DATENAME(DAY,DATEADD(DAY,-1,DATEADD(Month,1,'February 2009')))
This select query returns number of days in a particular month. You can put date in "MM/DD/YYYY" or "MM/DD/YY" format also.
There is an another version of Using SQL statement or SQL Query to calculating the Number of Days in a Month
select datepart(dd,dateadd(dd,-1,dateadd(mm,1,cast(cast(year(getdate()) as varchar)+'-'+cast(month(getdate()) as varchar)+'-01' as datetime))))
Example on May (month), you just copy and paste the SQL Query or SQL statement to the SQL Query Analyzer. You will get the answer is 31 days for May(month).

Monday, January 12, 2009

SQL Server 2000 vs Oracle 9i


Reference:- http://www.mssqlcity.com/Articles/Compare/sql_server_vs_oracle.htm
Alexander Chigrik
chigrik@mssqlcity.com



Introduction
Platform comparison

  • Hardware requirements



  • Software requirements


  • Performance comparison

  • TPC tests


  • Price comparison
    Features comparison

  • T-SQL vs PL/SQL



  • SQL Server 2000 and Oracle 9i limits


  • Conclusion
    Literature

    Introduction

    Often people in newsgroups ask about some comparison of Oracle and Microsoft SQL Server. In this article, I compare SQL Server 2000 with Oracle 9i Database regarding price, performance, platforms supported, SQL dialects and products limits.

    Platform comparison

    SQL Server 2000 only works on Windows-based platforms, including Windows 9x, Windows NT, Windows 2000 and Windows CE.
    In comparison with SQL Server 2000, Oracle 9i Database supports all known platforms, including Windows-based platforms, AIX-Based Systems, Compaq Tru64 UNIX, HP 9000 Series HP-UX, Linux Intel, Sun Solaris and so on.

    Hardware requirements

    To install SQL Server 2000, you should have the Intel or compatible platforms and the following hardware:

    Hardware Requirements
    Processor Pentium 166 MHz or higher
    Memory 32 MB RAM (minimum for Desktop Engine),
    64 MB RAM (minimum for all other editions),
    128 MB RAM or more recommended
    Hard disk space 270 MB (full installation),
    250 MB (typical),
    95 MB (minimum),
    Desktop Engine: 44 MB
    Analysis Services: 50 MB minimum and 130 MB typical
    English Query: 80 MB

    Oracle 9i supports Intel or compatible platforms, AIX-Based Systems, Compaq Tru64 UNIX, HP 9000 Series HP-UX, Linux Intel, Sun Solaris and so on.

    To install Oracle 9i under the Intel or compatible platforms, you should have the following hardware:

    Hardware Requirements
    Processor Pentium 166 MHz or higher
    Memory RAM: 128 MB (256 MB recommended)
    Virtual Memory: Initial Size 200 MB, Maximum Size 400 MB
    Hard disk space 140 MB on the System Drive
    plus 4.5 GB for the Oracle Home Drive (FAT)
    or 2.8 GB for the Oracle Home Drive (NTFS)

    To install Oracle 9i Database under the UNIX Systems, such as AIX-Based Systems, Compaq Tru64 UNIX, HP 9000 Series HP-UX, and Sun Solaris, you should have the following hardware:

    Hardware Requirements
    Memory A minimum of 512 MB RAM
    Swap Space A minimum of 2 x RAM or 400 MB, whichever is greater
    Hard disk space 4.5 GB

    Software requirements

    SQL Server 2000 comes in six editions: Enterprise, Standard, Personal, Developer, Desktop Engine, and SQL Server CE (a compatible version for Windows CE) and requires the following software:

    Operating System Enterprise Edition Standard Edition Personal Edition Developer Edition Desktop Engine SQL Server CE
    Windows CE No No No No No Yes
    Windows 9x No No Yes No Yes No
    Windows NT 4.0 Workstation with Service Pack 5 No No Yes Yes Yes No
    Windows NT 4.0 Server with Service Pack 5 Yes Yes Yes Yes Yes No
    Windows NT 4.0 Server Enterprise Edition with Service Pack 5 Yes Yes Yes Yes Yes No
    Windows 2000 Professional No No Yes Yes Yes No
    Windows 2000 Server Yes Yes Yes Yes Yes No
    Windows 2000 Advanced Server Yes Yes Yes Yes Yes No
    Windows 2000 DataCenter Yes Yes Yes Yes Yes No
    Windows XP Professional No No Yes Yes Yes No

    Oracle 9i Database comes in three editions: Enterprise, Standard and Personal and requires the following software:

    Platform Operating System Version Required Patches
    Windows-based Windows NT 4.0 Service Pack 5
    Windows-based Windows 2000 Service Pack 1
    Windows-based Windows XP Not Necessary
    AIX-Based AIX 4.3.3 Maintenance Level 09 and IY24568,
    IY25282, IY27614, IY30151
    AIX-Based AIX 5.1 AIX 5L release 5.1 ML01+ (IY22854),
    IY26778, IY28766, IY28949, IY29965, IY30150
    Compaq Tru64 UNIX Tru64 5.1 5.1 patchkit 4
    Compaq Tru64 UNIX Tru64 5.1A 5.1A patchkit 1
    HP-UX HP-UX version 11.0 (64-bit) Sept. 2001 Quality Pack, PHCO_23792,
    PHCO_24148, PHKL_24268, PHKL_24729,
    PHKL_ 25475, PHKL_25525, PHNE_24715,
    PHSS_23670, PHSS_24301, PHSS_24303,
    PHSS_24627, PHSS_22868
    Linux SuSE Linux Enterprise Server 7
    (or SLES-7) with kernel 2.4.7,
    and glibc 2.2.2
    Not Necessary
    Sun Solaris Solaris 32-Bit 2.6 (5.6), 7 (5.7)
    or 8 (5.8)
    Not Necessary
    Sun Solaris Solaris 64-Bit 8 (5.8) Update 5

    Performance comparison

    It is very difficult to make the performance comparison between SQL Server 2000 and Oracle 9i Database. The performance of your databases depends rather from the experience of the database developers and database administrator than from the database's provider. You can use both of these RDBMS to build stable and efficient system. However, it is possible to define the typical transactions, which used in inventory control systems, airline reservation systems and banking systems. After defining these typical transactions, it is possible to run them under the different database management systems working on the different hardware and software platforms.

    TPC tests

    The Transaction Processing Performance Council (TPC.Org) is independent organization that specifies the typical transactions (transactions used in inventory control systems, airline reservation systems and banking systems) and some general rules these transactions should satisfy.

    The TPC produces benchmarks that measure transaction processing and database performance in terms of how many transactions a given system and database can perform per unit of time, e.g., transactions per second or transactions per minute.

    The TPC organization made the specification for many tests. There are TPC-C, TPC-H, TPC-R, TPC-W and some old tests, such as TPC-A, TPC-B and TPC-D. The most popular test is the
    TPC-C test (OLTP test).

    At the moment the article was wrote, SQL Server 2000 held the top TPC-C by performance results with Distributed Partitioned Views-based cluster systems.
    See
    Top Ten TPC-C by Performance Version 5 Results

    At the moment the article was wrote, SQL Server 2000 held the top TPC-C by price/performance results. See
    Top Ten TPC-C by Price/Performance Version 5 Results

    Note. Because most organizations really do not run very large databases, so the key points on which SQL Server 2000 won the TPC-C benchmarks do not really matter to the vast majority of companies.

    Price comparison

    One of the main Microsoft SQL Server 2000 advantage in comparison with Oracle 9i Database is that SQL Server is cheaper. Other SQL Server advantage is that Microsoft includes the Online analytical processing (OLAP) and Data Mining as standard features in SQL Server 2000 Enterprise Edition. So, you can save up to four times with SQL Server 2000 Enterprise Edition if you use OLAP and Data Mining.

    The price comparisons below were based on the
    Oracle and SQL Server 2000 Price Comparison
    article from Microsoft.

    Compare pricing for SQL Server 2000 Standard Edition and Oracle9i Standard Edition:

    Number of CPUs Oracle9i Standard Edition SQL Server 2000 Standard Edition
    1 $15,000 $4,999
    2 $30,000 $9,998
    4 $60,000 $19,996
    8 $120,000 $39,992
    16 $240,000 $79,984
    32 $480,000 $159,968

    Compare pricing for SQL Server 2000 Enterprise Edition (which include OLAP and Data Mining) and Oracle9i Enterprise Edition with OLAP and/or Data Mining:

    Number of CPUs Oracle9i Enterprise Edition Oracle9i Enterprise Edition with OLAP or Data Mining Oracle9i Enterprise Edition With OLAP and Data Mining SQL Server 2000 Enterprise Edition
    1 $40,000 $60,000 $80,000 $19,999
    2 $80,000 $120,000 $160,000 $39,998
    4 $160,000 $240,000 $320,000 $79,996
    8 $320,000 $480,000 $640,000 $159,992
    16 $640,000 $960,000 $1,280,000 $319,984
    32 $1,280,000 $1,920,000 $2,560,000 $639,968

    Note. This is not a full price comparison between SQL Server 2000 and Oracle 9i Database. It is only a brief comparison. You can have any discounts and the prices can be increased or decreased in the future. See Microsoft and Oracle to get more information about the price of their products.

    Features comparison

    Both SQL Server 2000 and Oracle 9i Database support the ANSI SQL-92 entry level and do not support the ANSI SQL-92 intermediate level. In the Features comparison section of this article I want to make the brief comparison of the Transact-SQL with PL/SQL and show some SQL Server 2000 and Oracle 9i Database limits.

    T-SQL vs PL/SQL

    The dialect of SQL supported by Microsoft SQL Server 2000 is called Transact-SQL (T-SQL). The dialect of SQL supported by Oracle 9i Database is called PL/SQL. PL/SQL is more powerful language than T-SQL. This is the brief comparison of PL/SQL and T-SQL:

    Feature PL/SQL T-SQL
    Indexes B-Tree indexes,
    Bitmap indexes,
    Partitioned indexes,
    Function-based indexes,
    Domain indexes
    B-Tree indexes
    Tables Relational tables,
    Object tables,
    Temporary tables,
    Partitioned tables,
    External tables,
    Index organized tables
    Relational tables,
    Temporary tables
    Triggers BEFORE triggers,
    AFTER triggers,
    INSTEAD OF triggers,
    Database Event triggers
    AFTER triggers,
    INSTEAD OF triggers
    Procedures PL/SQL statements,
    Java methods,
    third-generation language
    (3GL) routines
    T-SQL statements
    Arrays Supported Not Supported

    SQL Server 2000 and Oracle 9i limits

    Here you can find some SQL Server 2000 and Oracle 9i Database limits:

    Feature SQL Server 2000 Oracle 9i Database
    database name length 128 8
    column name length 128 30
    index name length 128 30
    table name length 128 30
    view name length 128 30
    stored procedure name length 128 30
    max columns per index 16 32
    max char() size 8000 2000
    max varchar() size 8000 4000
    max columns per table 1024 1000
    max table row length 8036 255000
    max query size 16777216 16777216
    recursive subqueries 40 64
    constant string size in SELECT 16777207 4000
    constant string size in WHERE 8000 4000

    Conclusion

    It is not true that SQL Server 2000 is better than Oracle 9i or vice versa. Both products can be used to build stable and efficient system and the stability and effectiveness of your applications and databases depend rather from the experience of the database developers and database administrator than from the database's provider. But SQL Server 2000 has some advantages in comparison with Oracle 9i and vice versa.

    The SQL Server 2000 advantages:

    • SQL Server 2000 is cheaper to buy than Oracle 9i Database.
    • SQL Server 2000 holds the top TPC-C performance and price/performance results.
    • SQL Server 2000 is generally accepted as easier to install, use and manage.
    The Oracle 9i Database advantages:

    • Oracle 9i Database supports all known platforms, not only the Windows-based platforms.
    • PL/SQL is more powerful language than T-SQL.
    • More fine-tuning to the configuration can be done via start-up parameters.

    Literature

    1. SQL Server 2000 Books Online

    2.
    Oracle documentation

    3.
    Oracle and SQL Server 2000 Price Comparison

    4.
    Why is SQL Server better/worse than Oracle?

    5.
    Top Ten TPC-C by Performance Version 5 Results

    6.
    Top Ten TPC-C by Price/Performance Version 5 Results