FAQ : What is Partition Scheme in SQL Server?

Once you have created the Partition Function then the next step is to create Partition Scheme. As already mentioned, One partition function may have one or more Partition Scheme. . The partition scheme defines how any resulting partitions will be stored on filegroups. You list the filegroups for a partition scheme in the same order that you want to have them map to the partitions defined by the partition function. Creating a partition scheme assumes that you have already created the filegroups. You can mention ALL TO keyword to create all the partition to a single filegroup.

Eg. From BOL
CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000);
GO
CREATE PARTITION SCHEME myRangePS1
AS PARTITION myRangePF1
TO (test1fg, test2fg, test3fg, test4fg);

Edit : Should Filegroup single file or multiple files considered as out of scope in this context. But selecting single filegroup VS multiple filegroup and Single files per filegroup VS multiple files also a significant decision to be made in terms of performance.

FAQ : What is Partition Function in SQL Server?

Partition functions are special kind of objects (of course created by user) created in a database which can be used only by partition scheme. Partition function contains a data type , that must be the same data type as the partition column chosen in the table. Partition Function as such not directly related to any table.

NOTE: Even though Partition Function to Partition scheme is One to Many, in sliding window scenario moving data between in and out of partition is a common requirement, which require Partition function modification. So practically it makes each Partition table should have exclusive partition function and scheme.
Partition functions are not user defined functions.

Here are few of facts to be known about partition functions• Partition functions are not listed as database objects in the sys.all_objects or sys.objects system tables; instead, you can find them listed in sys.partition_functions.
• Partition functions are not contained by a database schema.
• Special commands must be used for creating, altering, and dropping a partition function:
o CREATE PARTITION FUNCTION
o ALTER PARTITION FUNCTION
o DROP PARTITION FUNCTION
• Partition functions can be invoked interactively in Transact-SQL by using the $PARTITION function.

Partition Functions System Objects

• sys.partition_functions : Contains a row for each partition function.
• sys.partition_range_values : Contains a row for each range boundary value of a partition function of type R.
• sys.partition_parameters : Contains a row for each parameter of a partition function.
• $PARTITION. :Returns the partition number into which a set of partitioning column values would be mapped for any specified partition function.
Eg. SELECT $PARTITION.RangePF1 (10) ;

FAQ : What is the advantages and disadvantages of Table Partitioning of 2005 over Partition View of earlier versions?

Advantage of Table Partitioning
• No need to create separate physical tables for each ranges manually.
• There are few limitations when you insert data into Partition View like you cannot use BULK INSERT/BCP etc.
• Less complex to manage and administrate
• SQL Server automatically manages the placement of data in the proper partitions.


Disadvantage of Table Partitioning
• Major disadvantage is, we cannot have different index model for different range. Ie. In general, we may need to have more number of indexes when the data is READ ONLY and less or different index model for the data which is READWRITE. That is not possible with partition table.
• You cannot rebuild a partitioned index with the ONLINE option set to ON, because the entire table will be locked during the rebuild.

Partition is not recommended for a table in following condition

1. If you want to have different indexing structure for READ ONLY and READ WRITE data then partition is not recommended.
2. If the table is not accessed much or do not required much index maintenance
3. If the query on the tables are filtered(WHERE condition) by some column which cannot be used for partitioned.
4. If you planning to use ONLINE INDEX REBUILD option you cannot use partition. You cannot rebuild a partitioned index with the ONLINE option set to ON, because the entire table will be locked during the rebuild.

FAQ: How to determine partition suits for a table ?

There are few general guidelines for determining whether the partition feature is suited for a table or not. One point to be noted here is, if a table is not partitioned properly (ie. Not selected the most suitable partition column), though it will not adversely affect the performance but it will not give any performance benefit either. Remember that the query optimizer can limit the partitions that are scanned to resolve a query only if the query filters on the partitioning column. Precisely, just because the table is partitioned, we will not have performance benefit.

1. First of all, just because of a table is big, it should not be partitioned. Ie. Only size of the table cannot be "THE" criteria for partition.
2. If the index maintenance on a table is time consuming or make the table offline for the users, you may opt partition because re-indexing on only required partition is better than re-indexing the whole table. Partitioning a large table divides the table and its indexes into smaller partitions, so that maintenance operations can be applied on a partition-by-partition basis, rather than on the entire table.
3. You can go for partition if the data must be aged out of the table (archived) periodically and the current delete process is taking time or blocking other users.
4. If the table is loaded with new data periodically and the loading process takes time and the table data lends itself to a partition column based on ascending date or time we may go for partition.
5. Table backup of huge table can be managed smartly by using new backup features of sql server 2005 and partitioning.
6. To summarize, the best way to put this in single statement is, if you have any table which fits into SLIDING WINDOW scenario you must partition it.

FAQ: What all are the types of table partitions in SQL Server

There are two types of partitions. Horizontal and vertical.

Horizontal partition
Horizontal partition divides the data on subset of rows. Ie. Divide the data in such a way that all the rows of single year /month /week(Range) may be in one partition kind. In earlier versions of SQL Server this method was achieved by creating physical tables for each subsets and UNION the tables using a View(Partition View) and application access the data using Views.

Vertical partition
In Vertical Partition , the columns of a very wide table are spread across multiple physical tables containing distinct subsets of the columns with the same number of rows. The relation would be ONE TO ONE among the tables. The result is multiple tables containing the same number of rows but different columns, usually with the same primary key column in each table. Often a view is defined across the multiple tables and queries directed against the view.

FAQ : What is Table Partitioning in SQL Server?

Partitioning is the process of dividing data into small , more manageable chunks. We can divide the data horizontally (on rows ) or vertically (on columns) . Table partitioning can make very large tables and indexes easier to manage, and improve the performance of APPROPRIATELY FILTERED queries. Horizontal Partitioning is one of the new feature added in SQL Server 2005 (in earlier versions we used to create separate physical table and create a wrapper view which union all the tables which is also known as Partition View). When a table is created as a partitioned table, SQL Server automatically places the table's rows in the correct partition, and SQL Server maintains the partitions behind the scenes. You can then perform maintenance operations on individual partitions, and properly filtered queries will access only the correct partitions. But it is still one table as far as SQL Server is concerned

Error : CREATE FILE encountered operating system error 32(The process cannot access the file because it is being used by another process.

One of the common reason for this error is, when we try to attach a MDF file which is already attached (to the same instance or any other instance) we get this error. So the solution is this, if you get this error when you try to attach a MDF file, then ensure that the file is not accessed by any other process or attached to any SQL Server instance already.

FAQ : How to disable AUTO UPATE STATISTICS for a specific table or index in SQL Server

Using sp_autostats system stored procedure we can switch off AUTO UPDATE STATISTICS on a specific table or index. Please note that, if AUTO UPDATE STAT is OFF in Database level, you can not switch ON object level. In otherwords, if the DB level Auto update stat is ON then you can override the DB level setting and switch off AUTO UPDATE STAT object level using this stored procedure.

Syntax
sp_autostats [ @tblname = ] 'table_name'
[ , [ @flagc = ] 'stats_flag' ]
[ , [ @indname = ] 'index_name' ]


Enabling automatic statistics for all indexes of a table
The following example enables the automatic statistics setting for all indexes of the Product table.

USE AdventureWorks;
GO
EXEC sp_autostats 'Production.Product', 'ON'

Community Technology Preview of Microsoft SQL 2008 R2 is available for download

The latest Community Technology Preview (CTP) for SQL Server 2008 R2 available to the general public today. The new capabilities in the latest CTP include support for Windows Server 2008 R2 – including Hyper-V with Live Migration – as well as enhanced data compression with support for Unicode UCS-2. Also included are new visualization features and a Report Part Gallery to Report Builder 3.0.



SQL Server 2008 R2 includes enhancements designed to help administrators centrally monitor and manage multiple database applications, instances or servers. In addition, it has enabled high-scale complex event-stream processing through SQL Server StreamInsight, and expanded business intelligence capabilities with SQL Server PowerPivot for Excel.



At the PASS Summit last week in Seattle, Microsoft announced two new premium editions – SQL Server 2008 R2 Datacenter and SQL Server 2008 R2 Parallel Data Warehouse – these will be available once SQL Server 2008 R2 is released. SQL Server 2008 R2 is on track for general availability in the first half of 2010.



The Community Technology Preview of Microsoft SQL 2008 R2 is available for download Here

FAQ : Why we need to have different service account for each service in SQL Server?

In SQL Server, as per the components we select, different services gets installed like SQL SERVER Database engine, Analysis Service, Integration Services, Reporting Services and SQL Server Agent Services. But each service have its own specific tasks to do which requires different privilege and permissions. If we are configuring all the services startup account with a single user, this user will have to have a sum of all the permission required by all the services, which is very against security policy recommended by Microsoft. For eg. DB Engine needs permission to the folder where the data and log files are kept why because it has to write the data into these files. Whereas SQL Server Agent does not need this permission. So, if we are using same user for both the services, if the SQL Server Agent service got hacked or compromised , even the database engine is at risk. This is the very basic reason why we need different windows user for each service.

SQL Server BI Webcast series by Amit Bansal

There is a webcast series starting soon on SQL Server BI by Amit Bansal.

Webcast 1: BI for you -November 6, Friday, 3 pm to 4.30 pm

Webcast 2: Create your first cube -November 9, Monday, 3 pm to 4.30 pm

Webcast 3: Extract data from the cube -November 11, Wednesday, 3 pm to 4.30 pm

Webcast 4: Learn MDX scripting -November 13, Friday, 3 pm to 4.30 pm



To register & browse more details about each webcast, visit

Check this nice article on A-Z Guide to Being an Architect

A-Z Guide to Being an Architect

FAQ : What is Domain of a column? How it defer from Datatype

Domain of a columm is the set of the values that are applicable for a column. For example, Gender column can have Female or Male so these value make the domain for Gender column.

Though Domain and Datatype looks similar but it is not so. Datatype is the type of data that can be stored in the column . ie. Integer datatype column can only store numeric data.

FAQ : What is the difference between Framework and Standard?

We have heard lot about ITIL and ISOS. Here ITIL is framework where as ISOS is Standard. What is the difference between standard and framework? Have you ever thought about this?

Standard : Standard is something very rigid which should be followed across. Standard will define proper method to do a process. ISOS is a standard.

Framework : Framework is something where you have the boundary defined. It is flexible and not always defined with step by step process. ITIL is a framework.

FAQ : What is SQL Server PRINT equivalent in Oracle

Print In SQL Server
Returns a user-defined message to the client. Mainly Print is used while debugging procedures or triggers.
Eg
PRINT N'This is from first Loop.';

Oracle Equivalent :
What can come close in Oracle is DBMS_OUTPUT.PUT_LINE. Dbms_output is a PL/SQL Package which has a put_line procedure which write data to flat file or to direct your PL/SQL output to a screen.

DBMS_OUTPUT.PUT_LINE ('This is from first Loop);

FAQ : What is Oracle Predefined Exceptions equivalent in SQL Server

What is Oracle Predefined Exceptions
An exception is a runtime error or warning condition, which can be predefined or user-defined. Predefined exceptions are raised implicitly (automatically) by the runtime system. User-defined exceptions must be raised explicitly by RAISE statements. To handle raised exceptions, you write separate routines called exception handlers. PL/SQL declares predefined exceptions globally in package STANDARD, which defines the PL/SQL environment. So, you need not declare them yourself.
Here are few examples
NO_DATA_FOUND : A SELECT INTO statement returns no rows, or your program references a deleted element in a nested table or an uninitialized element in an index-by table. SQL aggregate functions such as AVG and SUM always return a value or a null. So, a SELECT INTO statement that calls an aggregate function never raises NO_DATA_FOUND. The FETCH statement is expected to return no rows eventually, so when that happens, no exception is raised.

TOO_MANY_ROWS : A SELECT INTO statement returns more than one row.

DUP_VAL_ON_INDEX : Your program attempts to store duplicate values in a database column that is constrained by a unique index.

Read more about Predefined exceptions here

What is SQL Server Equivalent?

Till SQL Server 2008 we do not have any equivalent. Ie there is no Predefined Exceptions in SQL Server. But this can be achieved by BEGIN TRY .. END TRY BEGIN CATCH … END CATCH. ERROR Number/Message combination.

FAQ : Oracle %ROWTYPE equivalent in SQL Server

What is %ROWTYPE in Oracle?

The %ROWTYPE attribute provides a feature to create Table based or Cursor Based record. Ie. If you have a table with 5 column and you want to select all the five column value of a single record at a stretch instead of declaring 5 scalar variable you just declare a single variable of %ROWTYPE which will intern have all the five column. Consider we have a table called Department with three columns Department_ID, Description and Name. Either you can declare three scalar variable or you can declare single variable of %ROWTYPE attribute. If you want to store a collection (multiple records ) then you can use TABLE OF TableName%RowType;
See the below eg.
DECLARE
Dept_REC DEPARTMENT%ROWTYPE;
BEGIN
SELECT *
INTO Dept_REC
FROM DEPARTMENT
WHERE DEPARTMENT_ID = 1;
DBMS_OUTPUT.PUT_LINE (' DEPARTMENT ID: '|| Dept_REC.DEPARTMENT_ID);
DBMS_OUTPUT.PUT_LINE ('COURSE DESCRIPTION: '||
Dept_REC.DESCRIPTION);
DBMS_OUTPUT.PUT_LINE ('Name: '||
Dept_REC.Name);
END;

SQL Server Equivalent.

In SQL Server what can come close to TABLE OF TableName%RowType is Declare a table type variable (in case it is collection). Another way may be create a #temp table. But temp table method may involve IO which need to be aware of and also in there are limitation where you can create #temp table. For a single row, it is better to declare separate scalar variable for each columns

Table variable Method
Declare @Dept Table (Department_id Int,Name Varchar(100),Description Varchar(1000))
The below statement will insert all the rows from Department to @Dept variable.
Select *INTO @Dept
FROM
Department

Temp table method
Select *into #Temp_Dept
FROM Department
The above statement will create a temp table on the fly. This can be used in Procedure but not in Function.

FAQ : What is SQL Server Profiler equivalent in Oracle

What is SQL Server Profiler?

Microsoft SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database Engine or Analysis Services. You can capture and save data about each event to a file or table to analyze later. For example, you can monitor a production environment to see which stored procedures are affecting performance by executing too slowly. Basically this tool is a wrapper which internally calls SQL Trace system stored procedures. Generally, running profiler is not recommended in production if you need to trace events, then in production it is better to go for SQL Trace system Stored procedure. Refer this link for more info.

Oracle Equivalent . (please note that this is based on my little experience in Oracle and If anyone feels that If I am going wrong please feel free to correct me)

Automatic Workload Repository (AWR)

Oracle offers many ways to trace events which again later version may have new tools and features. In 10 G, Oracle offers Automatic Workload Repository (AWR) which is used to collect statistics including
• Wait events used to identify performance problems.
• Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.
• Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view.
• Some system and session statistics from the V$SYSSTAT and V$SESSTAT views.
• Object usage statistics.
• Resource intensive SQL statements.

Read more about AWR here

SQL trace files and TKPROF

If you are still in Oracle 9, you can go for SQL trace files which gives you statement by statement log of SQL and PL/SQL executed by Database Client(s). The SQL Trace facility and TKPROF are two basic performance diagnostic tools that can help you monitor and tune applications running against the Oracle Server. SQL Trace files entries can be classified in to mainly four categories.
• Database calls (parse, execute, and fetch)
• Wait events
• Bind variable values
• Miscellaneous events like timestamps, instance service name, session, module, action, and client
identifier

TKPROF

You can run the TKPROF program to format the contents of the trace file and place the output into a readable output file. Optionally, TKPROF can also:
• Determine the execution plans of SQL statements
• Create a SQL script that stores the statistics in the database
Read more about SQL Trace files and TKPROF here

FAQ: What is optimizer in SQL Server?

Unlike procedural language SQL Statements does not state the exact steps that database engine should follow to extract the data or manipulate the data. In procedural language the statements are ran as per the sequence they have written. But in SQL , database engine must analyse the statement to determine the most efficient way to extract/manipulate data. This process is called optimization and the component which does this process is called Optimizer.

The input to the optimizer contains , Query , schema (table/indexes definitions) and the statistics available.

The output from the optimizer is query execution plan.



The SQL Server query optimizer is a cost-based optimizer. Each possible execution plan has an associated cost in terms of the amount of computing resources used. The query optimizer must analyze the possible plans and choose the one with the lowest estimated cost. Some complex SELECT statements have thousands of possible execution plans. In these cases, the query optimizer does not analyze all possible combinations. Instead, it uses complex algorithms to find an execution plan that has a cost reasonably close to the minimum possible cost.

FAQ : What is Optimizing SQL statement .

Unlike procedural language SQL Statements does not state the exact steps that database engine should follow to extract the data or manipulate the data. In procedural language the statements are ran as per the sequence they have written. But in SQL , database engine must analyse the statement to determine the most efficient way to extract/manipulate data. This process is called optimization.

FAQ : What is the equivalent of SELECT * INTO of SQL Server in Oracle

SQL Server
Select * into TargettableName from sourceTableName

Oracle
Create table targetTableName as Select *From sourceTableName

FAQ : What is Packages in Oracle and its equivalent in SQL Server

A package is a schema object that groups logically related PL/SQL types, variables, and subprograms. Packages usually have two parts, a specification (spec) and a body; sometimes the body is unnecessary. The specification is the interface to the package. It declares the types, variables, constants, exceptions, cursors, and subprograms that can be referenced from outside the package. The body defines the queries for the cursors and the code for the subprograms.

There is no equivalent in SQL Server yet.

FAQ : How to declare a variable in SQL Server (T-SQL) and in Oracle PL/SQL

SQL Server

Variables are declared in the body of a batch or procedure with the DECLARE Statement and are assigned to values with either a SET or SELECT Statement.After declaration all variables are initialized as NULL
Variables are often used in batch or procedure /functions as counters for WHILE, LOOP or for an IF..ELSE block. Variables can be used only in expression, not in the place of object names or key words (in that case you need to use Dynamic SQL. See Dynamic SQL for more info).

Types of Variable : Three types of variables are available
• @local variable
• @Cursor_Variable_Name
• @table_variable_name

Declare a variable :
DECLARE @VariableName datatype : Variable prefixed with @ symbol.
Eg. DECLARE @Count INT
DECLARE @Name varchar(50), @Address varchar(200)

Seting /initialising a variable :

SET @Name=’Madhu’
SET @Count=100
Initialise multiple variable using Select statement :

SET can be used for only single variable where as SELECT can be used for multiple variable in a single statement

SELECT @Name=’Madhu’ , @Count=0 , @Address=’XYZ’

WHILE(@i<@Count)
BEGIN
SET @i=@i+1
PRINT @i
END

Oracle (PL/SQL) : Declaring Variables
In oracle, Variables can have any SQL datatype, such as CHAR, DATE, or NUMBER, or a PL/SQL-only datatype, such as BOOLEAN or PLS_INTEGER. For example, assume that you want to declare variables for part data, such as part_no to hold 6-digit numbers and in_stock to hold the Boolean value TRUE or FALSE. You declare these and related part variables as shown in Example 1. Note that there is a semi-colon (;) at the end of each line in the declaration section.

Example 1–1. Declaring Variables in PL/SQL
DECLARE
part_no NUMBER(6);
part_name VARCHAR2(20);

You can also declare nested tables, variable-size arrays (varrays for short), and records using the TABLE, VARRAY, and RECORD composite datatypes.

Assigning Values to a Variable
You can assign values to a variable in three ways. The first way uses the assignment operator (:=), a colon followed by an equal sign, as shown in Example 2. You place the variable to the left of the operator and an expression, including function calls, to the right. Note that you can assign a value to a variable when it is declared.

Example 1–2 Assigning Values to Variables With the Assignment Operator
DECLARE
wages NUMBER;
hours_worked NUMBER := 40;
emp_rec1 employees%ROWTYPE;
country := UPPER('Canada');


The second way to assign values to a variable is by selecting (or fetching) database values into it. In Example 1–3, 10% of an employee's salary is selected into the bonus variable. Now you can use the bonus variable in another computation or insert its value into a database table.

Example 1–3 Assigning Values to Variables by SELECTing INTO

DECLARE
bonus NUMBER(8,2);
emp_id NUMBER(6) := 100;
BEGIN
SELECT salary * 0.10 INTO bonus FROM employees
WHERE employee_id = emp_id;
END;

The third way to assign a value to a variable is by passing it as an OUT or IN OUT parameter to a subprogram, and then assigning the value inside the subprogram.

FAQ : How to declare a variable in SQL Server (T-SQL) and in Oracle PL/SQL

SQL Server

Variables are declared in the body of a batch or procedure with the DECLARE Statement and are assigned to values with either a SET or SELECT Statement.After declaration all variables are initialized as NULL
Variables are often used in batch or procedure /functions as counters for WHILE, LOOP or for an IF..ELSE block. Variables can be used only in expression, not in the place of object names or key words (in that case you need to use Dynamic SQL. See Dynamic SQL for more info).

Types of Variable : Three types of variables are available
• @local variable
• @Cursor_Variable_Name
• @table_variable_name

Declare a variable :
DECLARE @VariableName datatype : Variable prefixed with @ symbol.
Eg. DECLARE @Count INT
DECLARE @Name varchar(50), @Address varchar(200)

Seting /initialising a variable :

SET @Name=’Madhu’
SET @Count=100
Initialise multiple variable using Select statement :

SET can be used for only single variable where as SELECT can be used for multiple variable in a single statement

SELECT @Name=’Madhu’ , @Count=0 , @Address=’XYZ’

WHILE(@i<@Count)
BEGIN
SET @i=@i+1
PRINT @i
END

Oracle (PL/SQL) : Declaring Variables
In oracle, Variables can have any SQL datatype, such as CHAR, DATE, or NUMBER, or a PL/SQL-only datatype, such as BOOLEAN or PLS_INTEGER. For example, assume that you want to declare variables for part data, such as part_no to hold 6-digit numbers and in_stock to hold the Boolean value TRUE or FALSE. You declare these and related part variables as shown in Example 1. Note that there is a semi-colon (;) at the end of each line in the declaration section.

Example 1–1. Declaring Variables in PL/SQL
DECLARE
part_no NUMBER(6);
part_name VARCHAR2(20);

You can also declare nested tables, variable-size arrays (varrays for short), and records using the TABLE, VARRAY, and RECORD composite datatypes.

Assigning Values to a Variable
You can assign values to a variable in three ways. The first way uses the assignment operator (:=), a colon followed by an equal sign, as shown in Example 2. You place the variable to the left of the operator and an expression, including function calls, to the right. Note that you can assign a value to a variable when it is declared.

Example 1–2 Assigning Values to Variables With the Assignment Operator
DECLARE
wages NUMBER;
hours_worked NUMBER := 40;
emp_rec1 employees%ROWTYPE;
country := UPPER('Canada');


The second way to assign values to a variable is by selecting (or fetching) database values into it. In Example 1–3, 10% of an employee's salary is selected into the bonus variable. Now you can use the bonus variable in another computation or insert its value into a database table.

Example 1–3 Assigning Values to Variables by SELECTing INTO

DECLARE
bonus NUMBER(8,2);
emp_id NUMBER(6) := 100;
BEGIN
SELECT salary * 0.10 INTO bonus FROM employees
WHERE employee_id = emp_id;
END;

The third way to assign a value to a variable is by passing it as an OUT or IN OUT parameter to a subprogram, and then assigning the value inside the subprogram.

FAQ : How to find Product version in SQL Server and in Oracle

SQL Server

Select @@Version
Or
SET NOCOUNT ON;
SELECT
CONVERT( varchar, SERVERPROPERTY('Edition')) AS Edition
, CONVERT( varchar, SERVERPROPERTY('ProductVersion')) AS Version
, CONVERT( varchar, SERVERPROPERTY('ProductLevel')) AS Level


Oracle

select * From V$VERSION ;
Note : V$VERSION is the catalog view which provides all the information in Oracle

FAQ : What is String summary statistics in SQL Server?

SQL Server 2005 introduces additional information collected by statistics created on char, varchar, varchar(max), nchar, nvarchar, nvarchar(max), text, and ntext columns. This information, called a string summary, helps the query optimizer estimate the selectivity of query predicates on string patterns . This helps the optimizer better estimate the selectivity of conditions that use the LIKE operator. String summary information is not maintained if the summary for a column sample is larger than the Database Engine can maintain. For example, a string summary will not be maintained on statistics created by using WITH FULLSCAN on a unique varchar(80) column with 80 characters in each string, almost no similarity between strings, on a table with 85,000 rows. To determine whether a string summary is stored for a particular statistics object, use DBCC SHOW_STATISTICS (Transact-SQL) .

FAQ : What is Equi-JOIN and Non-Equi JOIN in SQL

What is Equi-Join?

When a JOIN is made on Exact match between two columns it is called equi-Join.
Eg.
SELECT A.*
FROM EMPLOYEES A, EMPLOYEE_SAL B
WHERE A.EMP_ID=B.EMP_ID
OR
SELECT A.*
FROM EMPLOYEES A
INNER JOIN EMPLOYEE_SAL B
ON A.EMP_ID=B.EMP_ID
Both the queries mentioned above can be considered as Equi-join.

What is NON-Equi Join.

If you use any other comparison operator other than = in JOIN condition, it is called NON-Equi JOIN
SELECT A.*
FROM EMPLOYEES A, EMPLOYEE_SAL B
WHERE A.EMP_ID <> B.EMP_ID

FAQ : What is WITH ROLLBACK ROLLBACK IMMEDIATE | AFTER integer [SECONDS] | NO_WAIT in ALTER DATABASE in SQL Server

When you ALTER a database state what to do with the current transaction(s) (If any) on the database are defined by these statements.

WITH ROLLBACK IMMEDIATE

WITH ROLLBACK IMMEDIATE tell the DB engine to rollback all the transaction immediately and make the DB into the given state in the Alter Statement. Ie
In my database, there is a transaction running for last one hour and it is not yet committed and now I want to make this database into SINGLE_USER. The following line of Alter statement will do that for me
-- Set the database to single user
Alter Database MyDatabase set SINGLE_USER WITH ROLLBACK IMMEDIATE

The above statement will rollback the current transaction immediately and bring the DB to Single_USER

WITH ROLLBACK AFTER integer [SECONDS]

This command tells DB engine to wait for the specified seconds before rolling back all the transaction. If the transaction still not committed in the given seconds then rollback.

ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK AFTER 30

The above statement will bring the database to SINGLE_USER after 30 sec. If any transaction is there after 30 sec it will rollback.


WITH NO_WAIT
This is bit different from the above two statements. This tells the DB engine to stop or terminate ALTER Statement rather than terminating or rollback the transaction. Ie. If any active transaction is there on the database, the ALTER Statement will fail.

ALTER DATABASE MyDatabase SET SINGLE_USER WITH NO_WAIT

FAQ : What is Database in SQL Server and Oracle?

Database in SQL Server.
A Microsoft SQL Server database is a collection of entities that stores and manipulate data. There are two types of Databases in SQL Server, System Databases and User Databases. There are four system databases which is mandatory in any instance. An instance can have max 32767 user databases which is not practical in real time scenario. But instances with 100s of DBs may be common. A database in SQL Server is made up of a collection of tables. These tables contain data and other objects, such as views, indexes, stored procedures, user-defined functions, and triggers that are defined to support activities performed with the data. The data stored in a database is typically related to a particular subject or process, such as inventory information for a manufacturing warehouse.


Database in Oracle
In Oracle the database is physical structure that consists of files stored on disks . Oracle’s database structures include tablespaces, control files, redo log files, archived logs, block change tracking files, Flashback logs, and recovery backup (RMAN) files etc.

One point to note here is, in general , the database definition may looks like similar in both SQL Server and Oracle but the way it implemented is little different. Oracle generally, will have single database and different schema for each User (read department) . And Oracle provides a feature to backup /restore only single schema /user. But in SQL Server though there is schema, but this backup/restore feature is not available by default (yes there is a tweaking but not recommended). So in SQL Server we may create separate database for each Department/user. In that case keeping FK relation across database is not possible.

FAQ : What is an instance in SQL Server and Oracle

SQL Server Instance :
In SQL Server you can have multiple instance running on same physical machine. It means, you will have multiple database engine (windows services) running on the same machine. Each instance of the SQL Server database engine has its own set of system and user databases that are not shared between instances. Each instance will have its own memory area and other resources. There are two types on instances in SQL Server, Default instance and Named Instance. You will have only one Default instance on a machine but may have more than one (depends upon the edition of SQL Server what you have, it can be anything upto 50) Named Instance on a machine.

Why we need multiple instance of SQL Server?
The reason can be technical or non-technical (political). Ie. If you want to have more resources allocated for a particular department(read database(s)) than other department(s) you may go for different instances per department (Exception : In SQL Server2008 onwards there is resource governor feature which can control resource allocation). Another technical reason would be if one of your applications is highly tempdb intensive then you may go for different instances because each instance will have its own tempdb.

Political reason is, generally SA privilege may not be sharable between departments and each department would like to have its own SA, then you may go for different instances.

Oracle Instance.
Instance in Oracle is not similar to Instance in SQL Server. Generally, people uses Instances and Databases in Oracle as synonyms which is incorrect. One need to understand the distinction to understand the Oracle Architecture. To explain Instance in Oracle you need to understand what is Database in oracle too. In short, Databases are PHYSICAL and Instances are LOGICAL. Instance consists of memory structures and process in the server. Oracle has an area of shared memory called System Global Area (SGA) and each process have its own private memory area called Program Global Area (PGA). Instance to Database is ONE TO ONE. Ie An Instance can be part of only one Database but database can have multiple Instances (I never see some thing like this in my short stint as Oracle developer).

Why you would go for multiple instance in Oracle?
I would like to have the answer for this question from viewer. I do have some idea, but not convinced as the way SQL Server reasoning for Multiple instance on same machine.

FAQ : SQL Server 2005 Error, OBJECT_SCHEMA_NAME is not a recognized built-in function name

OBJECT_SCHEMA_NAME function was newly introduced in SQL Server 2005 SP2. If you have not applied SP2 you will have "OBJECT_SCHEMA_NAME is not a recognized built-in function name" error.

Download and Install SP2 to solve this problem.

FAQ : How to import MS Excel 2007 data to SQL Server

Import data from Office 2007 Excel to SQL Server

SELECT * INTO YourTableName FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\YourExcelfilename.xlsx', [Sheet1$])

Import data from Office 2003 Excel to SQL Server

SELECT * INTO YourTableName
FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0','Excel 8.0; Database=c:\bb.xls',
'SELECT * FROM [Sheet1$]') AS XL

Tech Ed India 2009

Tech Ed India 2009 at Hyderabad brings to you the best of the IT Pro technologies in-depth coverage! Check here

Visual Representation of SQL Joins

Excellent article on Joins