Data -The Universal Base

How to pass array parameters into oracle stored procedure

2011-09-23T23:24:00.000-07:00

There are various ways in which you could handle passing multiple records to oracle stored procedures. The old style of handling this was passing the values as xml and process the xml by the procedure. This adds lots of overhead to the oracle processors. An alternate to this is to pass the array in to the procedure and iterate and insert the values to the table. this approach may be more meaningful for the web applications with UI and middle tier separated out from database layer. This approach will significantly reduce the number of calls made to the database layer from middle tier. from the .Net development environment, ODP.Net gives good options to bind your parameter array to stored procedures . You could leverage that for better efficiency.

Steps i have scripted below are
1. Creating a table to store the values
2. Create a package with the type and the procedure to insert the values to table using the array.
3. PL SQL script to try executing the procedure from the sqlplus.

-- Drop the existing test table
Drop table CPM_TESTTABLE;
-- Create the new test table
Create table CPM_TESTTABLE
(COL_NAME varchar2(20) ,
COLN_NAME1 varchar2(10));

--Create the package with the type and the procedure
CREATE OR REPLACE PACKAGE ARRAY_PKG AS
TYPE char_array1 IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;

PROCEDURE SPM_INS_TESTTABLE (
pColName IN char_array1,
pColName1 IN char_array1);
END ARRAY_PKG;

CREATE or REPLACE package body ARRAY_PKG as
PROCEDURE SPM_INS_TESTTABLE
(
pColName IN char_array1,
pColName1 IN char_array1
)
IS

BEGIN

FOR I IN 1..PCOLNAME.COUNT
LOOP
INSERT INTO CPM_TESTTABLE
(COL_NAME,COLN_NAME1)
VALUES
(pColName(i),pColName1(i));

END LOOP;

EXCEPTION
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END SPM_INS_TESTTABLE;

END ARRAY_PKG;

-- PL sql script to execute the procedure with array inout parameter
Declare
Customer_name ARRAY_PKG.char_array1;
Begin
-- Adding 10 rows to the array
for i in 1..10 Loop
customer_name(i) := 'Ciju '|| to_char(i);
End Loop;
delete from CPM_TESTTABLE ; -- Useful while re re-running the test
-- Call the procedure using array input
ARRAY_PKG.SPM_INS_TESTTABLE (customer_name , customer_name);
End;
commit;
SELECT * FROM CPM_TESTTABLE
-- End of script

Oracle - SYS_REFCURSOR Variable value listing

2010-10-12T06:03:00.000-07:00

First step is to create a procedure with out parameter datatype SYS_REFCURSOR
-- Procedure creation
CREATE OR REPLACE procedure cp_test1 (pgnum int ,pgrecords int ,pResultSet OUT SYS_REFCURSOR) is
Begin
Open pResultSet for
select *
from ( select a.*, rownum r
from ( select *
from test1
order by id ) a
where rownum < (pgnum * pgrecords)+1)
where r > ((pgnum-1) * pgrecords);

End;

After the procedure is created, please try below statements in SQL plus:
sql > set serveroutput on
sql > variable r1 refcursor
sql > exec cp_test1(1,20,:r1)
sql > print :r1

Oracle - Search nodes of tree structure using connect by

2010-10-12T05:57:00.000-07:00

Finding --Bottom to top relation
select entityunitid, entityname
from table name
connect by prior parentunitid = entityunitid
start with entityunitid = bottom id

--Top to Bottom relation
select entityunitid, entityname
from table name
connect by prior entityunitid = parentunitid
start with entityunitid = top id

Change an existing primary key for table having records; with a new identity column

2009-11-16T23:09:00.000-08:00

Steps to change an existing primary key for table having records with a new identity column :

-- Create a sample table with primary key

Create table test3 ( id int primary key, name char(2))

-- Insert some records to this table

Insert into test3 values (1,'cc')

Insert into test3 values (2,'cq')

Insert into test3 values (3,'cw')

-- Add a new column ( this will be set primary key in subsequent steps)

Alter table test3 add new_id bigint

-- Get and drop the primary key constraint (to drop already created primary key)

declare @cons_name varchar(300);

Select @cons_name = name from sysobjects where xtype = 'PK' and parent_obj in ( select id from sysobjects where xtype = 'U' and name = 'test3')

exec (' Alter table test3 Drop constraint ' + @cons_name) ;

-- Update the new column with the sequence number

Update test3 set test3.new_id = tt.a

From (

Select row_number() over (order by name) as a ,name from test3) tt

Where test3.name = tt.name

-- Set the column as not null

alter table test3 alter column new_id bigint not null

-- Set the new column as primary key

alter table test3 add primary key (new_id)

Select * from test3

-- You could set the identity key to next value using dbcc_checkindent

-- DBCC CHECKIDENT ("table name", RESEED, 300)

TSQL enhancements in SQL server 2005 # Part 1

2009-10-04T23:03:00.000-07:00

1. TOP clause with TIES option : Select TOP(4) with TIES * from test order by idkey desc

2. Supports an OUTPUT clause so a single trip to the server performs the data updateand returns the results.

3. PIVOT operator provides the ability to quickly and easily generate cross tab queries. A cross tab query rotates rows data into columns data. : select VENDORID , [7],[8],[9] from MonthlyPurchaseOrders pivot (sum(subtotal) for Ordermonth in ( [7],[8],[9],[10])) as a

4. Exception Handling with TRY/CATCH

5. ROW_NUMBER() function creates a column that displays a number corresponding the row's position in the query result : select name, row_number() over (order by name) from test

6. RANK() function works much like the ROW_NUMBER() function in that it numbers records in order. They differ in the way they work when duplicate values are contained in the ORDER BY expression. : select name, row_number() over (order by name) , rank() over ( order by name) from test

7. DENSE_RANK() works the same way as RANK() does but eliminates the gaps in the numbering.

8. NTILE() breaks the result set into a specified number of groups and assigns the same number to each record in a group

9. Common Table Expressions (CTE) A Common Table Expression (CTE) is a temporary result set created from a simple query

Using NOLOCK and READPAST table hints in SQL Server

2009-09-21T04:01:00.000-07:00

NOLOCK

This table hint, also known as READUNCOMMITTED, is applicable to SELECT statements only. NOLOCK indicates that no shared locks are issued against the table that would prohibit other transactions from modifying the data in the table.

The following example shows how NOLOCK works and how dirty reads can occur. In the script below, I begin a transaction and insert a record in the SalesHistory table.

SELECT COUNT(*) FROM SalesHistory WITH(NOLOCK)
 The number of records returned is 301. Since the transaction that entered the  record into the SalesHistory table has not been committed, I can undo it. I'll  roll back the transaction by issuing the following statement:
ROLLBACK TRANSACTION
 This statement removes the record from the SalesHistory table that I  previously inserted. Now I run the same SELECT statement that I ran earlier:
SELECT COUNT(*) FROM SalesHistory WITH(NOLOCK)
 This time the record count returned is 300. My first query read a record that  was not yet committed -- this is a dirty read.

READPAST
 This is a much less commonly used table hint than NOLOCK. This hint specifies  that the database engine not consider any locked rows or data pages when  returning results
The READPAST table hint example is very similar to the NOLOCK table hint  example. I'll begin a transaction and update one record in the SalesHistory  table.
BEGIN TRANSACTION
      UPDATE TOP(1) SalesHistory
      SET SalePrice = SalePrice + 1
 Because I do not commit or roll back the transaction, the locks that were  placed on the record that I updated are still in effect. In a new query editor  window, run the following script, which uses READPAST on the SalesHistory table  to count the number of records in the table.
SELECT COUNT(*)
FROM SalesHistory WITH(READPAST)
 My SalesHistory table originally had 300 records in it. The UPDATE statement  is currently locking one record in the table. The script above that uses  READPAST returns 299 records, which means that because the record I am updating  is locked, it is ignored by the READPAST hint.

SQL 2005 - Save a password not as clear text

2009-09-16T22:44:00.000-07:00

In SQL Server 2005, you can use HashBytes to hash the password and EncryptByKey to encrypt it.

Examples

A. Simple symmetric encryption

The following code shows how to encrypt a column by using a symmetric key.

USE AdventureWorks;

--If there is no master key, create one now.

IF NOT EXISTS

(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)

CREATE MASTER KEY ENCRYPTION BY

PASSWORD = '23987hxJKL969#ghf0%94467GRkjg5k3fd117r$$#1946kcj$n44nhdlj'

CREATE CERTIFICATE HumanResources037

WITH SUBJECT = 'Employee Social Security Numbers';

CREATE SYMMETRIC KEY SSN_Key_01

WITH ALGORITHM = AES_256

ENCRYPTION BY CERTIFICATE HumanResources037;

USE [AdventureWorks];

-- Create a column in which to store the encrypted data.

ALTER TABLE HumanResources.Employee

ADD EncryptedNationalIDNumber varbinary(128);

-- Open the symmetric key with which to encrypt the data.

OPEN SYMMETRIC KEY SSN_Key_01

DECRYPTION BY CERTIFICATE HumanResources037;

-- Encrypt the value in column NationalIDNumber with symmetric

-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.

UPDATE HumanResources.Employee

SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber);

-- Verify the encryption.

-- First, open the symmetric key with which to decrypt the data.

OPEN SYMMETRIC KEY SSN_Key_01

DECRYPTION BY CERTIFICATE HumanResources037;

-- Now list the original ID, the encrypted ID, and the

-- decrypted ciphertext. If the decryption worked, the original

-- and the decrypted ID will match.

SELECT NationalIDNumber, EncryptedNationalIDNumber

AS 'Encrypted ID Number',

CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))

AS 'Decrypted ID Number'

FROM HumanResources.Employee;

Output option in SQL 2005

2009-09-09T06:28:00.000-07:00

"Output" provides you access to inserted and deleted logical tables. The data that has been inserted into the table OR the data after update statement has been executed, is available in inserted logical table.

declare @temptable table
(
empid int ,
name varchar(60)
)
UPDATE AllEmployees set NAME = 'upd'
OUTPUT inserted.employee_id , inserted.name into @temptable
WHERE Employee_id = 9

select * from @temptable

SQL 2005 use level for hierarchy select

2009-09-09T06:08:00.000-07:00

To get all the people in the hierarchy starting from Top management we could use the the following query:

Create table AllEmployees ( Employee_id int primary key identity , name varchar(100), ManagerId int)
select * from AllEmployees
insert into AllEmployees(name,managerid) values ( E1, null)
insert into AllEmployees(name,managerid) values ( E2, null)
insert into AllEmployees(name,managerid) values ( 'E3' , 1)
insert into AllEmployees(name,managerid) values ( 'E4' , 2)
insert into AllEmployees(name,managerid) values ( 'E5' , 1)
insert into AllEmployees(name,managerid) values ( 'E6' , 2)
insert into AllEmployees(name,managerid) values ( 'E7' , 4)
insert into AllEmployees(name,managerid) values ( 'E8' , 4)
With LvlAllemployees as
(
select Allemployees.employee_id, Allemployees.name , Allemployees.managerid, 0 as level from AllEmployees where ManagerId is null
union ALL
select Allemployees.employee_id, Allemployees.name , Allemployees.managerid, level+1 as level from LvlAllEmployees
inner join Allemployees on LvlAllemployees.employee_id = allemployees.managerid
)
select * from Lvlallemployees order by level

View contents of a Stored Procedure (quickly)

2009-07-20T22:13:00.000-07:00

USe the procedure ‘SP_HELPTEXT ’ to view the content of the stored rpocedure from T-SQL . If you are extensive user of the T-SQL stored procedure, you may assing ehte short key from Tools -> Options -> Keyboard and type in SP_HELPTEXT for a key of your choice.

Extract Data from Stored Procedure output into a Table in SQL

2009-07-15T23:49:00.000-07:00

How to "Extract Data from Stored Procedure output into a Table in SQL"

There could be many ways to do this. If you are using SQL Server 2008 it would be better to have your stored procedure return a table variable.

If you are running prior versions best could be to use "INSERT INTO #tmpTable EXEC spStoredProcedure" method

Calling Stored Procedure on Linked Server

2009-07-15T22:40:00.000-07:00

SQL SERVER – Executing

Remote Stored Procedure – Calling Stored Procedure on Linked Server - SQL 2000

Prerequirement : Create and configure the linked server

Syntax to call the procedure :

EXEC [RemoteServer] .DatabaseName.DatabaseOwner.StoredProcedureName

Please make sure the RPC is checked in the property tab of Linked server (Server opt

ion tab)

Usefull SET statements in SQL 2005

2009-07-09T23:40:00.000-07:00

Different SET statments that could be useful during development :
SET CONCAT_NULL_YIELDS_NULL : Controls whether concatenation results are treated as null or empty string values.
SET ARITHABORT : Terminates a query when an overflow or divide-by-zero error occurs during query execution
SET FMTONLY : Returns only metadata to the client. Can be used to test the format of the response without actually running the query
SET IDENTITY_INSERT : Allows explicit values to be inserted into the identity column of a table
SET LANGUAGE : Specifies the language environment for the session. The session language determines the datetime formats and system messages.
SET NOEXEC : Compiles each query but does not execute it. This could be used to check the syntax with out executing the actual SQL.
SET SHOWPLAN_TEXT / SET SHOWPLAN_XML: Causes Microsoft SQL Server not to execute Transact-SQL statements. Instead, SQL Server returns detailed information about how the statements are executed.
SET STATISTICS TIME : Displays the number of milliseconds required to parse, compile, and execute each statement.
SET STATISTICS XML : Causes Microsoft SQL Server to execute Transact-SQL statements and generate detailed information about how the statements were executed in the form of a well-defined XML document.

Top 10 Hidden Gems in SQL Server 2005

2009-07-09T22:34:00.000-07:00

Top 10 Hidden Gems in SQL Server 2005
Published: January 9, 2007 By Cihan Biyikoglu
SQL Server 2005 has hundreds of new and improved components. Some of these improvements get a lot of the spotlight. However there is another set that are the hidden gems that help us improve performance, availability or greatly simplify some challenging scenarios. This paper lists the top 10 such features in SQL Server 2005 that we have discovered through the implementation with some of our top customers and partners.
The order in the list does not have much significance except the specific instances we used them and the impact we saw. I will use a practical analogy; I started with the utility-knife size features that can help make life very easy at the right moment and build up to chain-saw size features that can help you implement a full scenario.

TableDiff.exe
Table Difference tool allows you to discover and reconcile differences between a source and destination table or a view. Tablediff Utility can report differences on schema and data. The most popular feature of tablediff is the fact that it can generate a script that you can run on the destination that will reconcile differences between the tables. TableDiff.exe takes 2 sets of input;
Connectivity - Provide source and destination objects and connectivity information.
Compare Options - Select one of the compare options
Compare schemas: Regular or Strict
Compare using Rowcounts, Hashes or Column comparisons
Generate difference scripts with I/U/D statements to synchronize destination to the source.
TableDiff was intended for replication but can easily apply to any scenario where you need to compare data and schema.
You can find more information about command line utilities and the Tablediff Utility in Books Online for SQL Server 2005.
Triggers for Logon Events (New in Service Pack 2)
With SP2, triggers can now fire on Logon events as well as DML or DDL events.
Logon triggers can help complement auditing and compliance. For example, logon events can be used for enforcing rules on connections (for example limiting connection through a specific username or limiting connections through a username to a specific time periods) or simply for tracking and recording general connection activity. Just like in any trigger, ROLLBACK cancels the operation that is in execution. In the case of logon event that means canceling the connection establishment. Logon events do not fire when the server is started in the minimal configuration mode or when a connection is established through dedicated admin connection (DAC).
The following code snippet provides an example of a logon trigger that records the information about the client connection.CREATE TRIGGER connection_limit_trigger
ON ALL SERVER FOR LOGON
AS
BEGIN
INSERT INTO logon_info_tbl SELECT EVENTDATA()
END;
You can find more information about this feature in updated Books Online for SQL Server Services Pack 2 un the heading “Logon Triggers”.
Boosting performance with persisted-computed-columns (pcc).
Btree Indexes provide great compromise for tuning queries vs redundant storage of data and added cost of modifying data (insert/update/delete). A less known capability for tuning in SQL Server 2005 is persisted computed columns (PCC). Computed columns can help you shift the runtime computation cost to data modification phase. The computed column is stored with the rest of the row and is transparently utilized when the expression on the computed columns and the query matches. You can also build indexes on the PCC’s to speed up filtrations and range scans on the expression.
The following sample can demonstrate the benefits of a persisted computed column applied to a complex expression. The same TSQL query run against the following table schema with and without the DayType column will demonstrate the effect of the transparent expression matching with persisted computed columns. The output from the sys.dm_exec_query_stats DMV also shows the difference in the IO and CPU characteristics of the query.
QuerySELECT [Ticker] ,[Date] , [DayHigh] ,[DayLow] ,[DayOpen] ,[Volume] ,[DayClose] ,[DayAdjustedClose],
CASE
WHEN volume > 200000000 and dayhigh-daylow /daylow > .05 THEN 'heavy volatility'
WHEN volume > 100000000 and dayhigh-daylow /daylow > .03 THEN 'volatile'
WHEN volume > 50000000 and dayhigh-daylow /daylow > .01 THEN 'fair'
ELSE 'light'
END as [DayType]
FROM dbo.MarketData
WHERE
CASE
WHEN volume > 200000000 and dayhigh-daylow /daylow > .05 THEN 'heavy volatility'
WHEN volume > 100000000 and dayhigh-daylow /daylow > .03 THEN 'volatile'
WHEN volume > 50000000 and dayhigh-daylow /daylow > .01 THEN 'fair'
ELSE 'light'
END = 'heavy volatility'
Table SchemaCREATE TABLE [dbo].[MarketData](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[Ticker] [nvarchar](5) NOT NULL,
[Date] [datetime] NOT NULL,
[DayHigh] [decimal](38, 6) NOT NULL,
[DayLow] [decimal](38, 6) NOT NULL,
[DayOpen] [decimal](38, 6) NOT NULL,
[Volume] [bigint] NOT NULL,
[DayClose] [decimal](38, 6) NOT NULL,
[DayAdjustedClose] [decimal](38, 6) NOT NULL,
-- PERSISTED COMPUTED COLUMN --
[DayType] AS (
CASE
WHEN volume > 200000000 and dayhigh-daylow /daylow > .05 THEN 'heavy volatility'
WHEN volume > 100000000 and dayhigh-daylow /daylow > .03 THEN 'volatile'
WHEN volume > 50000000 and dayhigh-daylow /daylow > .01 THEN 'fair'
ELSE 'light'
END) PERSISTED NOT NULL
) ON [PRIMARY]
Output From The Sys.Dm_Exec_Query_Stats Dynamic Management View (DMV)

.
In the above picture, the output from sys.dm_exec_query_stats dynamic management view shows the difference in CPU and IO statistics between the same query hitting MarketData_Computed and MarketData tables. Line 1 represents the query run against the table with the persisted computed column. Line 2 is the table without the persisted computed column. With the complex expression pre-calculated in the DayType column, total worker time and overall elapsed time is lower compared to the table without the DayType persisted computed column.
•Another way to verify that the persisted computed column is utilized, is to use the execution plan and look at the scan or the seek operator for the table with the computed column and check the output list, which should contain the column. In the example below you can see the DayType, the name for the PCC, in the output list under #9.
DEFAULT_SCHEMA setting in sys.database_principles
SQL Server provides great flexibility with name resolution. However name resolution comes at a cost and can get noticeably expensive in adhoc workloads that do not fully qualify object references. SQL Server 2005 allows a new setting of DEFEAULT_SCHEMA for each database principle (also known as “user”) which can eliminate this overhead without changing your TSQL code. Here is an example;
In SQL Server 2005, the following query when executed by user1 that has a DEFAULT_SCHEMA of ‘dbo’ will directly resolve to dbo.tab1, instead of the extra search for user1.tab1. SELECT * FROM tab1
Whereas the same query will search for ‘user1.tab1’ in SQL Server 2000 and if that does not exist it will resolve to ‘dbo.tab1’.
This setting can be especially useful for databases upgraded from SQL Server 2000 to SQL Server 2005. To preserve the original behavior, databases upgraded from SQL Server 2000 will get the username as the DEFAULT_SCHEMA for each database principle. That means, in a database upgraded from a previous version to SQL Server 2005, ‘user1’ will get a DEFAULT_SCHEMA values of ‘user1’. To take advantage of the performance benefits, administrators can set the DEFAULT_SCHEMA through ALTER USER command and change it to the schema that most of the of the objects reside. Be aware this may break queries that may be utilizing objects in other schemas than the one set in the DEFAULT_SCHEMA setting and has not qualified the object names.
DEFULT_SCHEMA is documented in Book Online under the “CREATE USER (Transact-SQL)” heading.
Forced Parameterization
Parameterization allows SQL Server to take advantage of query plan reuse and avoid compilation and optimization overheads on subsequent executions of similar queries. However there are many applications out there that, for one reason or another, still suffer from ad-hoc query compilation overhead. For those cases with high number of query compilation and where lowering CPU utilization and response time is critical for your workload, force parameterization can help.
Force parameterization forces most queries to be parameterized and cached for reuse in subsequent submissions. Forced parameterization will remove the literal values and replaces them with parameters. This minimizes the compilation overhead for queries that are the same except the literal values in the query text. Forced parameterization is typically enabled at the database level. However it is also possible to hint FORCED PARAMETERIZATION on individual queries.
In a number of cases, we have witnessed improvements in performance up to 30% due to forced parameterization. However forced parameterization can cause inappropriate plan sharing in cases where a single execution plan does not make sense. For those cases, you can utilize features like plan guides or query hints.
You can find more information on Forced Parameterization in Books Online.
Vardecimal Storage Format
In Service Pack 2, SQL Server 2005 adds a new storage format for numeric and decimal datatypes called vardecimal. Vardecimal is a variable-length representation for decimal types that can save unused bytes in every instance of the row. The biggest amount of savings come from cases where the decimal definition is large (like decimal(38,6)) but the values stored are small (like a value of 0.0) or there is a large number of repeated values or data is sparsely populated.
SQL Server 2005 also includes a stored procedure that can estimate the savings before you enable the new storage format.master.dbo.sp_estimate_rowsize_reduction_for_vardecimal ‘tablename’
To enable vardecimal storage format, you need to first allow vardecimal storage on the database;exec sys.sp_db_vardecimal_storage_format N'databasename', N'ON'
Once the database option is enabled, you can then turn on vardecimal storage at a table level using the following procedure;exec sp_tableoption 'tablename', 'vardecimal storage format', 1
Vardecimal storage format presents an overhead due to the complexity inherent in variable length data processing. However in IO bound workloads, savings on IO bandwidth due to efficient storage can far exceed this processing overhead.
If you would like more information on this topic, updated SQL Server 2005 Books Online for Service Pack 2 contains extensive information on the new vardecimal format.
Indexing made easier with SQL Server 2005
The new Dynamic Management Views have improved monitoring and trouble shooting greatly. A few of the dynamic management views (DMVs) deserve special attention.
Through sys.dm_index_usage_stats you can find out how much maintenance and traversal you have for each index. Indexes with high maintenance numbers and low traversal numbers can be considered as good candidates for dropping.
Through sys.dm_db_missing_index_* collection of DMVs, you can get recommendations on what new indexes could benefit the queries running on your server. The recommendations come with a estimate on how much improvement you can expect from the new index.
If you’d like to automate creation and dropping of indexes, SQL Server Query Optimization Team has blogged about how to automate index recommendations into actions: http://blogs.msdn.com/queryoptteam/archive/2006/06/01/613516.aspx
Figuring out the most popular queries in seconds
Another great DMV that can help save you a lot of work is sys.dm_exec_query_stats. In previous version of SQL Server to find out the highest impact queries on CPU or IO in system, you had to walk through a long set of analyses steps including getting aggregated information out of the data you collected from profiler.
With sys.dm_exec_query_stats, you can figure out many combinations of query analyses by a single query. Here are some of the examples;
Find queries suffering most from blocking – (total_elapsed_time – total_worker_time)
Find queries with most CPU cycles – (total_worker_time)
Find queries with most IO cycles – (total_physical_reads + total_logical_reads + total_logical_writes)
Find most frequently executed queries – (execution_count)
You can find more information on how to use dynamic management views for performance troubleshooting in the “SQL Server 2005 Waits and Queues” whitepaper located at: http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/performance_tuning_waits_queues.mspx
Scalable Shared Databases
Scalable Shared Databases provide an alternative scale out mechanism for Read-Only environments. Through Scalable Shared Databases one can mount the same physical drives on commodity machines and allow multiple instances of SQL Server 2005 to work off of the same set of data files. The setup does not require duplicate storage for every instance of SQL Server and allows additional processing power through multiple SQL Server instances that have their own local resources like cpu, memory, tempdb and potentially other local databases. However this type of setup does limit the IO bandwidth since all instances point to the physical set of files.
Book Online for SQL server 2005 contains details on Scalable Shared Databases.
Soft-NUMA
Highly concurrent workloads hit a contention point around global state they maintain at some point. That point in many cases happen to be ‘8’. One way around this contention has been to eliminate the global state and create hierarchies. NUMA architectures allow us to eliminate the contention around global resources by moving main resources closer to each other and forming nodes. SQL Server 2005 recognizes the NUMA architecture and self manages allocation of resources to adhere and take advantage of the hardware NUMA setup at the time of startup. By aligning with the HW setup SQL Server partitions its internal management to improve throughput.
Some workloads benefit greatly from the partitioning concept, especially mixed workloads that have to run varying characteristics of data access concurrently (example: OLTP and Reporting). Soft-NUMA allows partitioning configuration to be extended into the software level and defined either on top of a NUMA enabled environment to further divide the hardware partitions into smaller chunks or on a machine that does not utilize NUMA concepts to enable partitioning for the configuration. By configuring partitions through Soft-NUMA, the administrator can control the allocation of schedulers and memory managers for each node and can configure specific TCP/IP ports for the nodes. Then, clients can be configured to connect using the specific ports to access specific partitions.
Soft-NUMA topic is extensively covered in Book Online. You can also read more about the details of Soft-NUMA at Slava Oks’s Weblog at http://blogs.msdn.com/slavao/

More details from

http://technet.microsoft.com/en-us/library/cc917696.aspx

Web Based Database Administration for SQL Server

2009-07-02T05:13:00.000-07:00

Microsoft SQL Web Data Administrator - Microsoft SQL Web Data Administrator application is a free tool from to perform typical SQL Server administration and development tasks. You have the ability to perform the following tasks:

Database, table, stored procedure, relational object, etc. creation, modification and deletion
Manage security
Import data and export relational objects

Download and install the Microsoft SQL Web Data Administrator from - http://www.microsoft.com/downloads/details.aspx?familyid=C039A798-C57A-419E-ACBC-2A332CB7F959&displaylang=en
Run setup.msi to start the installation process.
Launching the Interface - Navigate to Start All Programs Microsoft SQL Web Data Administrator SQL Web Data Administrator.
Launching the Application - Once the interface loads, select the applicable port or just press the 'Start' button.
Login - Specify the SQL Server instance, the authentication mode then press the 'Login' button.

Follow the menu options to make the basic SQL administrations.

example screen shot :

Display of Client Statistics - to find the execution time for select statements.

2009-07-02T04:23:00.000-07:00

This could be used as an option to find the execution time for select statements:

When you execute a script or query in the Transact-SQL editor, you can choose to collect client statistics for each execution of the script or query. You use the client statistics to gather information about execution times, the amount of data sent between client and server, and so on

To turn on or off the gathering of client statistics
Open a Transact-SQL editor session.
On the Data menu, point to T-SQL Editor, and click Include Client Statistics.
The menu item is a toggle. The first click turns on the gathering of client statistics, and the second click turns it back off. You can also toggle client statistics by clicking Include Client Statistics on the T-SQL Editor toolbar or by right-clicking in the T-SQL editor and then clicking Include Client Statistics.
Execute a query in the T-SQL editor.

If you turned on client statistics, the Client Statistics tab appears in the Results pane. If you turned off client statistics, the Client Statistics tab does not appear.

You could find details on this from : http://msdn.microsoft.com/en-us/library/aa833228.aspx

2009-06-26T02:29:00.000-07:00

Creation of linked server from SQL Server Management Studio
1. Open SQL server Management Studio
2. Connect to the database where you would like to create the linked server.
3. Expand the note Sever Objects

4. Choose the option New linked server.
5. Update the below details

a. Update the linked server name (This will be the name of the Linked server)
b. Set the provider name as specified in the screen shot

c. Enter the product name
d. Specify the Data source as
Driver={SQL Server};Database=databasename;Server=servername;UID=userid;PWD=password;

Notes : if you are facing any issues with accessing the database using the credentials provided , click on the Security tab and choose the security content and set the credentials.

sql 2005 Linked server error

2009-06-26T02:06:00.000-07:00

Why am i getting the error in Linked server :
Msg 7313, Level 16, State 1, Line 1
An invalid schema or catalog was specified for the provider "SQLNCLI" for linked server "Xyz".

Mostly thie error appear when the link table referance is not made correctly. The select statement should refer the linked server table as
linkserver name.database.dbo.table.

Debug procedures in SQL 2005

2009-06-25T04:37:00.000-07:00

Steps to Debug a stored procedure in sql 2005

1. Open the Microsoft Visual Studio 2005
2. Select Server Explorer option from Menu as follows:

3. From the Data Connections node, 'Add connection'.

4. Expand the data connection navigate to the Stored Procedures node.

5. Select the intended SP to be debugged.

6. Right click and to view the Stored procedure.

7. Place a break point on the intended line of code where debugging needs to be started.

10. Right click on the stored procedure select 'Step-Into Stored Procedure' as shown below.

11. This action brings parameter screen.

12. Enter the needful values and click Ok.

Return the name and deaprtment in single column using coalesce and function

2009-06-23T01:52:00.000-07:00

-- Create some temp tables
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DEPT]') AND type in (N'U'))
DROP TABLE [dbo].[DEPT]
GO
CREATE TABLE DEPT (ID INT , DEPT VARCHAR(100))
INSERT INTO DEPT VALUES (10,'ACCOUNTING')
INSERT INTO DEPT VALUES (20,'MARKETING')
INSERT INTO DEPT VALUES (30,'FINANCE')
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SNAME]') AND type in (N'U'))
DROP TABLE [dbo].[SNAME]
GO
CREATE TABLE SNAME (ID INT , DEPT INT , SNAME VARCHAR(100))
INSERT INTO SNAME VALUES (1,10,'HARISH')
INSERT INTO SNAME VALUES (2,20,'HARISH')
INSERT INTO SNAME VALUES (2,20,'CIJU')
INSERT INTO SNAME VALUES (2,30,'CIJU')
INSERT INTO SNAME VALUES (5,10,'CIJU')
GO
-- Drop and create the Functions for Comma seperated value return.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[COMA_CP1]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[COMA_CP1]
GO
CREATE FUNCTION COMA_CP1(@SNAME1 VARCHAR(100)) RETURNS VARCHAR(2000)
AS
BEGIN
DECLARE @RES VARCHAR(100)
SELECT @RES = COALESCE(@RES + ', ', '') + DEPT1.DEPT
FROM SNAME
INNER JOIN DEPT DEPT1
ON SNAME.DEPT = DEPT1.ID
WHERE SNAME = @SNAME1
RETURN @SNAME1 +','+ @RES
END
GO
-- Final select to Return
SELECT DBO.COMA_CP1(SNAME)
FROM SNAME
INNER JOIN DEPT DEPT1
ON SNAME.DEPT = DEPT1.ID
GROUP BY SNAME

LPAD function in sql server

2009-06-19T06:10:00.000-07:00

In PL/SQL we have the statement LPAD(string, length, pad), RPAD(string, length, pad)Returns string padded on left or right to length characters using the pad string as padding. Here is an example that provied the similar functioanlity in sql server

declare @in_val int
set @in_val = 1
select 'R'+right(replicate('0',3)+CAST(@in_val AS VARCHAR),2)
If you pass 1 as input val this will return 'R01' , for value 10 this will return 'R10'

Distinct xtype values in sysobjects

2009-06-11T22:30:00.000-07:00

xtype values in sysobjects
This is a list of all of the possible values for the xtype column in the sysobjects table of a SQL Server database:
C - CHECK constraint
D - Default or DEFAULT constraint
F - FOREIGN KEY constraint
L - Log
P - Stored procedure
PK - PRIMARY KEY constraint
RF - Replication filter stored procedure
S - System table
TR - Trigger
U - User table
UQ - UNIQUE constraint
V - View
X - Extended stored procedure

To get the current user in TSQL - SQL 2005

2009-06-04T08:06:00.000-07:00

To get current user run following script in Query Editor

SELECT SYSTEM_USER

SYSTEM_USER will return current user. From Book On-Line – SYSTEM_USER returns the name of the currently executing context. If the EXECUTE AS statement has been used to switch context, SYSTEM_USER returns the name of the impersonated context.

Native XML Web Services for Microsoft SQL Server 2005

2009-05-11T09:43:00.000-07:00

Intro :
Microsoft SQL Server 2005 provides a standard mechanism for accessing the database engine using SOAP via HTTP. Using this mechanism, you can send SOAP/HTTP requests to SQL Server to execute:

Transact-SQL batch statements, with or without parameters.
Stored procedures, extended stored procedures, and scalar-valued user-defined functions.

Requirements

SQL Server 2005-native Web services require Microsoft Windows Server 2003 as the operating system, because they rely on the kernel mode http driver http.sys that this version provides. Since SQL Server leverages the kernel mode http.sys driver, you do not necessarily need to have IIS installed to expose Web services out of SQL Server; this simplifies administration. Instead, you should base your decision to install IIS on application requirements. For example, certain applications benefit from having an explicit middle tier. In such cases, IIS would be useful.

CREATE HTTP ENDPOINT

HTTP Endpoints are created and administered using Transact-SQL DDL. Creating an HTTP Endpoint is the first step in enabling HTTP/SOAP access to SQL Server 2005. Each endpoint has a name and a collection of options that when combined define the behavior of the endpoint.

To illustrate how the CREATE HTTP ENDPOINT is used, let's take a look at a Hello World example for invoking a stored procedure via SQL Server Web Services.
First, create a stored procedure called hello world in the master database, using the following T-SQL. This stored procedure simply displays the string provided in the input parameter.

CREATE PROCEDURE hello_world(@msg nvarchar(256))AS

BEGIN
select @msg as 'message'
END

Next, use the following T-SQL to create the HTTP endpoint which will allow access to this stored procedure as a WebMethod:

CREATE ENDPOINT hello_world_endpoint
STATE = STARTEDAS HTTP ( AUTHENTICATION = ( INTEGRATED ),
PATH = '/sql/demo', PORTS = ( CLEAR ))
FOR SOAP ( WEBMETHOD 'http://tempuri.org/'.'hello_world'
(NAME = 'master.dbo.hello_world'),
BATCHES = ENABLED,
WSDL = DEFAULT )

All endpoints are stored in master, in the metadata view master.sys.http_endpoints. An endpoint doesn't have any SOAP Methods unless you define them. In the above example, we exposed the stored procedure master.dbo.hello_world as WebMethod 'hello_world'; the WebMethod can have any name and, for example, could have been called as 'testproc1' under the 'http://tempuri.org' namespace. Specifying DEFAULT as the value for WSDL clause enables the endpoint to respond to requests for WSDL generating WSDL using the default format. You can suppress WSDL generation by setting WSDL=NONE in the above statement. We discuss the details of WSDL generation in a subsequent section

WSDLWSDL is a document written in XML that describes a Web service. It specifies the location of the service and the operations (or methods) the service exposes. WSDL provides the information necessary for a client to interact with a Web service. Tools such as Visual Studio .NET and Jbuilder use the WSDL to generate proxy code that client applications can use to communicate with a Web service. If the endpoint has WSDL enabled, that endpoint will produce WSDL when it receives a request for it. The endpoint created earlier in this article will produce WSDL when an authenticated request is sent to it. A WSDL request is a simple HTTP get request of the form.

http://servername/sql/demo?wsdl

For more details Turn to : http://msdn.microsoft.com/en-us/library/ms345123.aspx