NET : Create Dynamic SqlCommand Parameters

string IDs = "2055,1644,5889";

List<string> IDNumbers = IDs.Split(',').ToList<string>();

StringBuilder sb = new StringBuilder();

cmd = conn.CreateCommand();

cmd.CommandType = CommandType.Text;

sb.Append("SELECT * FROM TABLE WHERE (");

//build parameters

foreach (string idNumber in IDNumbers)

{

sb.Append("TABLE.ID = " + ":parm" + idNumber.ToString());

sb.Append(" OR ");

}

sb.Remove(sb.Length - 3, 3);

sb.Append(") ");

foreach (string idNumber in IDNumbers)

{

cmd.Parameters.Add(new OracleParameter("parm" + idNumber.ToString(), idNumber));

}

cmd.ExecuteNonQuery();

SQL : Get Last Update TimeStampe For A Table

I have some Oracle tables that have no datestamp on them that would display the last update of that row.  Here is a query for that:

SELECT SCN_TO_TIMESTAMP( ORA_ROWSCN )

FROM YOURTABLE;

Sample output would look something like:

4/16/2014 9:01:26.000000000 AM

Of course, you can add a where clause to determine update for your particulars.

For more information: SCN_TO_TIMESTAMP

SQL Server : Using Quotes In Linked Server Queries

Doing an OpenQuery across a linked server can get ugly very fast and it is often best to avoid them.

The basic syntax of an OpenQuery is :

SELECT * FROM OPENQUERY(YourLinkedServerName, 'SELECT FIELD1, FIELD2 FROM YOURTABLE')

From a coding perspective, this gets ugly if you have to incorporate string parameters; thus, utilizing many quotes.  For Example:

SELECT * FROM OPENQUERY(YourLinkedServerName, 'SELECT FIELD1, FIELD2 FROM YOURTABLE WHERE FIELD1 = ''ID105'' ')

Microsoft made this easier with the built-in stored procedure that allows you to avoid multi-layered quotes:  sp_executesql

The above can become:

DECLARE @PARAM char(5)

SELECT  @PARAM = 'CA105'

EXEC YourLinkedServer.master.dbo.sp_executesql

N'SELECT FIELD1, FIELD2 FROM YOURTABLE WHERE FIELD1 = @p1',

N'@p1 char(5)',

@PARAM

But, what if your WHERE clause becomes:

WHERE FIELD2 in (''ID105'',''ID107'',''ID109'',''ID112'',''ID116'',

''ID119'',''ID400'',''ID404'',''ID101'',''ID102'',''ID115'',''ID106'',''ID111'',''ID117'',''ID412'',''654Z'',''ID103'',''ID121'',''ID401'',''ID403'',''ID407'',''ID108'',''ID113'',''ID114'',''ID120'',''ID123'',''ID405'',''ID409'',''ID100'',''ID110'',''ID124'',''ID408'',''ID104'',''ID118'',''ID406'',

''ID413'')

There are multiple ways to go about this but here is one cheap method for dealing with the quotes:

DECLARE @TSQL NVARCHAR(2000);

DECLARE @CODES NVARCHAR(500);

DECLARE @OPENQUERY NVARCHAR(100);

DECLARE @LINKED_SQL NVARCHAR(2700)

SET @CODES = '''ID105'',''ID107'',''ID109'',''ID112'',''ID116'',

''ID119'',''ID400'',''ID404'',''ID101'',''ID102'',''ID115'',''ID106'',''ID111'',''ID117'',''ID412'',''654Z '',''ID103'',''ID121'',''ID401'',''ID403'',''ID407'',''ID108'',''ID113'',''ID114'',''ID120'',''ID123'',''ID405'',''ID409'',''ID100'',''ID110'',''ID124'',''ID408'',''ID104'',''ID118'',''ID406'',

''ID413''';

SELECT @TSQL = 'SELECT … WHERE FIELD2 IN (‘+ @CODES + ')'  

-- CLEAN UP THE QUOTES FOR THE OPENQUERY

-----------------------------------------------------                 

SET @TSQL = REPLACE(@TSQL, '''','''''')  ;

SET @TSQL = @TSQL + ''')';

SET @OPENQUERY = 'SELECT * FROM OPENQUERY(GHDB, ''';

SET @LINKED_SQL = (@OPENQUERY+@TSQL)

EXEC sp_executesql @LINKED_SQL

SQL Server : Simple XQuery Using .nodes() With CROSS APPLY

--simple xquery example utilizing .nodes() with a CROSS APPLY

SELECT

X.value('OrderId[1]', 'VARCHAR(25)') As [orderid],

X.value('Status[1]', 'VARCHAR(10)') As [status]

FROM YourTable

CROSS APPLY xmlField.nodes('head/body/response/orders/order') AS T(X)

WHERE

X.value('Status[1]', 'VARCHAR(10)') ='O'

SQL Server : Using $partition Function

Recently, I needed to validate date-range value on each partition on a partitioned table.  To do this, I utilized the SQL Server "$Partition" function.  Per MSDN, "$Partition returns the partition number into which a set of partitioning column values would be mapped for any specified partition function."

 

The syntax is:

 

[ database_name. ] $PARTITION.partition_function_name(expression)

 

This handy function can also be used to obtain column information, row counts, and other information you might want to know per partition:

 

--STEP ONE:

--Get the name of your partition function:

SELECT NAME FROM SYS.partition_functions

 

NAME

TableAPartitionFunc

 

--STEP TWO:

--Get the name of the column from which your table is partitioned

SELECT c.name AS PartitionedColumnName

FROM  sys.tables t

JOIN  sys.indexes  i

ON (i.object_id = t.object_id

AND i.index_id < 2)

JOIN  sys.index_columns  ic

ON(ic.partition_ordinal > 0

AND ic.index_id = i.index_id and ic.object_id = t.object_id)

JOIN  sys.columns c

ON (c.object_id = ic.object_id

AND c.column_id = ic.column_id)

WHERE t.object_id = LOWER(object_id('yourtablename'))

 

PartitionedColumnName

DatePort

 

--STEP THREE:

--Use acquired data to query your partition values:

 

SELECT DISTINCT $partition.TableAPartitionFunc(DatePort) AS PartitionNumber,

CONVERT(VARCHAR(8), DatePort, 1) AS RangedDate, 'yourtablename' AS PartitionedTable

from yourtablename

ORDER BY $partition. TableAPartitionFunc(DatePort)

 

PartitionNumber	RangedDate	PartitionedTable
1	5/13/2013	yourtablename
2	5/14/2013	yourtablename
3	5/15/2013	yourtablename
4	5/16/2013	yourtablename

 

SQL Server : Difference Between SP_SEND_DBMAIL & XP_SENDMAIL

Recently, I decided to send email notifications via SQL Server’s XP_SENDMAIL() if a stored procedure were unable to complete its processing.  For the same stored procedure, I was ALSO rolling back the transaction in the event of failure. However, I noticed that whenever the transactions failed, then I would not get the email.

After some research, I discovered that email messages sent using XP_SENDMAIL()  would be rolled back (not sent) if the transaction is rolled back.  However, I learned that if I were to use another SQL Server method, SP_SEND_DBMAIL(), then the email would be sent regardless of the end result of the transaction.

Therefore; in sum, if you want the email message to be sent regardless of the end result of the transaction you'll need to use SP_SEND_DBMAIL().   Also, since XS_SENDMAIL() is apparently going to be deprecated, SP_SEND_DBMAIL may be the way to go for you. 

It appears that other important differences between the two email methods are:

1.      XP_SENDMAIL requires a MAPI client installed, such as Outlook, on the server. This is the only option for SQL Server 2000 and before.

2.      SP_SEND_DBMAIL is an SMTP solution, added for SQL Server 2005 and later.

Happy coding.

SQL Server : Query For A Quick Overview Of Your Partitions

-- =============================================

-- This is a basic query intended to provide

-- a quick look at partition info.  

-- =============================================

 

SELECT b.name as 'table_name', a.partition_number,

a.[rows] as 'row_count', a.partition_id,

a.object_id, a.hobt_id,

b.schema_id, c.name as 'schema_name', 

a.index_id, b.create_date, b.modify_date

FROM  sys.partitions AS a INNER JOIN

sys.objects AS b ON a.object_id = b.object_id

INNER JOIN sys.schemas AS c On c.schema_id = b.schema_id              

WHERE LOWER(b.name) = 'yourPartitionedTableName'

--pull only first index for a simpler view

and a.index_id < 2

ORDER BY b.name, partition_number;

 

-- =============================================

--Helpful info for a deeper dive

-- =============================================

 

SELECT * FROM SYS.partition_range_values ;

SELECT * from sys.partitions;

SELECT * FROM SYS.partition_functions;

SELECT * FROM sys.partition_schemes;

SELECT * from sys.schemas;

SELECT * FROM sys.filegroups;

 

--query sys.partitions to view and verify that the table contains partitions as expected

SELECT * FROM sys.partitions where OBJECT_NAME(Object_Id)='yourPartitionedTableName';

SELECT * FROM sys.objects where OBJECT_NAME(Object_Id)='yourPartitionedTableName';

SQL Server : Query Definition of an Object

-- Another way to query the definition of an object:

SELECT OBJECT_NAME(object_id), *

FROM sys.sql_modules

WHERE LOWER(DEFINITION) LIKE '%tbl%'

SQL: Introduction To Partitioning

When a database table grows in size to hundreds of gigabytes or more, it can become more difficult to load new data, remove old data, and maintain indexes. Just the sheer size of the table causes simple operations to take much longer than they might otherwise. Data that must be loaded or removed can be very sizable, making INSERT and DELETE operations on the table impractical. Database software, such as is provided with SQL Server, provides table partitioning to make such operations more efficient.

If your database has a very large table, you can "partition" this large table onto separate "filegroups."  Filegroups are special types of files that allows a user to divide data onto different disks for the purpose of optimizing query performance and maintenance operations.  For example, say you have a tall file cabinet where you keep lists of customer names. As the number of files grow, the longer it takes to locate any given file.  Database tables operate on the same principle. 

The first thing you will need to do when planning out a partitioned table is to decide how you will divide the table up.  As with our file cabinet example, we might decide to create one file cabinet where last names begin with A – F, another cabinet for files G – N, and another for files O – Z.  Likewise, our table might also be divided up using last names as a way to organize.  You can see how faster it would be to locate the last name "Jones" in one of three cabinets instead of a giant cabinet.

As business changes and data grows, partitioning can become more complicated; however, the example above does provide a basic understanding as to how partitioning can save time and resources.

SQL : "Synonym Swap" Design Pattern

Frequently, I get asked to explain a database design pattern involving "synonym-swapping."  Though I'm getting better at clarifying this approach, it is still often difficult for the business-side folks to really understand.  I will now attempt to write a post to direct these people so that they do not have to listen to my ramblings.

First, database synonyms are a type of "alias", or alternate name for a table, view, sequence, or other schema objects in a database.  Some people refer to them as "pointers"  because sometimes a synonym might point to one table and at another time it will point to a different table.

Synonyms are great ways of referencing one or more tables in your database.  You can reference one or more tables by a single name. For example, say you had table that contained orders for your company.  Your orders table has millions of rows and needs to be accessed 24-hours a day.  However, what if updating the data in the table takes a couple of hours?   Also, what if maintenance takes a few hours?  Your users do not have the time to wait or suffer through a performance degradation.  What will you do?

1. Create two tables that are exact duplicates of each other ("ORDERS_TABLE_A", "ORDERS_TABLE_B").  Then create two synonyms, "Orders_Staging","Orders."  The "Orders" synonym will ALWAYS point to the table with the latest data.

2. Say "Orders" is currently pointing to "ORDERS_TABLE_A" but the data needs refreshed.  You will update "ORDERS_TABLE_B" then "swap" the synonym to point to "ORDERS_TABLE_B."  The time it takes to toggle a synonym between two tables is almost a split second.   If something goes awry during the data refresh, then the synonyms never get swapped and the user has no interruption of service either way.
Synonyms are a very useful tool as it means that in your application does not have to worry about changing the  table names from "ORDERS_TABLE_A" to "ORDERS_TABLE_B" and vice versa every time you do a refresh.

As you can see Synonyms are very useful tools for using aliases and can help when you have processes that involve building new tables to the side of existing ones for performance reasons.

MSDN Synonyms

An Even Better Explanation

SQL: Introduction to Databases

For the sake of the more non-technical people, a database is a computer full of spreadsheets.  Anytime you've looked at data in a grid-format, then you've looked at a spreadsheet at some point.  For example, Microsoft Excel is the most popular spreadsheet software among office folks.  Why do people use databases if they can just look at data in a spreadsheet?  There are many of wonderful uses of databases, but the most necessary can be narrowed down to two:  size and reporting.

SIZE

Data gets big very quickly.  In Excel 2007, a user can enter 16,384 columns and 1,048,576 rows.  This seems like allot of data!  Your computer very well might crash if you tried to open an Excel spreadsheet containing a million rows, not to mention the amount of hard-drive space this would take!

A database is a dedicated piece of software created specifically for large amounts of data.  Usually, a database will run on a computer that contains only databases.

REPORTING

Businesses operate on data.  A business might use an Excel spreadsheet to keep track of the cost of office supplies.  Another business might have to keep track a chain of store inventories s across the country and what inventory is in each store.  They also might want to know what product sells more in the Midwest as opposed to the West Coast.  Data is useless without a means to make sense of it all.   Databases provide such means in a cost-effective and efficient manner.    With proper setup, a query into a database can search through millions of rows to find the one piece of information that you want and do it in a matter of milliseconds.

Databases can be as simple or as complex as you can imagine.  From cell phones to ATMs -- all are dependent on data systems.  The world runs on data. Now you know what I am referring to when I talk about databases.

SQL Server : Use XQuery On XML Data Types

XQuery is to XML what SQL is to database tables.   XQuery is designed to query XML data - not just XML files, but anything that can appear as XML, including databases.  In SQL Server, there are five methods that you can use to speed up your xquery to a column of type “xml:”  query(), value(), exist(), modify(), and nodes().  I’ll cover the first, and most commonly used, three.

For the purpose of this example, I will use the following sample xml in field “XmlField :”

<msg>

  <head>  

    <Id>X9356G356Y</Id>

  </head>

  <body>

    <ReturnedResponse>

      <Response>

        <ReturnCode>0</ReturnCode>      

        <Message>SUCCESS</Message>

      </Response>    

      <Customer>

   <CustomerId>CID459283</CustomerId>

        <State>NV</State>

 <Name>
  <FirstLast>Name1_Name2 </FirstLast>

        </Name>

      </Customer>

    </ReturnedResponse>

  </body>

</msg>

--XPATH Examples

--.value

SELECT  A.TransDate, A.MainId,

XmlField.value('/msg[1]/body[1]/ReturnedResponse[1]/Customer[1]/CustomerId[1]', 'VARCHAR(25)') as xCustomerId

FROM TABLE2 B WITH (NOLOCK)

INNER JOIN TABLE1 A WITH (NOLOCK) ON B.APk = A.MainId

WHERE

A.MainId = 'HI05491882'

--.exist

SELECT  A.TransDate, A.MainId,

XmlField.value('/msg[1]/body[1]/ReturnedResponse[1]/Customer[1]/CustomerId[1]', 'VARCHAR(25)') as xCustomerId

FROM TABLE2 B WITH (NOLOCK)

INNER JOIN TABLE1 A WITH (NOLOCK) ON B.APk = A.MainId

WHERE

A.MainId = 'HI05491882'

AND XmlField.exist('/msg/body/ReturnedResponse/Customer/CustomerId') = 1

--.query

SELECT XmlField.query('/msg/body/ReturnedResponse/Customer/CustomerId')

FROM TABLE2 B WITH (NOLOCK)

INNER JOIN TABLE1 A WITH (NOLOCK) ON B.APk = A.MainId

WHERE

A.MainId = 'HI05491882'

SQL Server: Get RowCount Of All Tables Without the Overhead

Avoid overhead with this completely awesome SQL Server query that returns the row-count of all tables without actually having to query each table.  Instead, it just pulls from the statistics:

SELECT '[' + SCHEMA_NAME(t.schema_id)+ '].[' + t.name + ']' AS full_name,

SCHEMA_NAME(t.schema_id) AS schema_name,

t.name AS table_name,

i.rows

FROM sys.tables AS t INNER JOIN

sys.sysindexes AS i ON t.object_id =i.id AND i.indid < 2

SQL Server: Get First and Last Days Of Month

DECLARE @min DATETIME

DECLARE @max DATETIME

DECLARE @now DATETIME

SET @now = GETDATE();

--get first day of last month:

SET @min = CONVERT(VARCHAR(25),DATEADD(dd

,-(DAY(DATEADD(mm,1,@now))-1),DATEADD(mm,-1,@now)),101) 

--get the last day of last month

SET @max = CONVERT(VARCHAR(25),DATEADD(dd

,-(DAY(@now)),@now),101) 

--get the first day of current month

SET @min = CONVERT(VARCHAR(25),DATEADD(dd

,-(DAY(@now)-1),@now),101)  

--get the last day of current month

SET @max = CONVERT(VARCHAR(25),DATEADD(dd

,-(DAY(DATEADD(mm,1,@now))),DATEADD(mm,1,@now)),101) 

SQL Server : Check If Index Exists On Schema

--create function:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[IndexExistsOnSchema]

(

      @SCHEMA VARCHAR(50),

      @INDEX  VARCHAR(255)

)

RETURNS INT

AS

BEGIN

      DECLARE @CNT INT

      SET @CNT = 0

      SELECT @CNT = COUNT(*)

      FROM SYS.INDEXES

      WHERE OBJECT_ID = OBJECT_ID(@SCHEMA)

      AND name = @INDEX

      RETURN @CNT

END 

GO

--use function:

IF (SELECT DBO.INDEXEXISTSONSCHEMA(@SCHEMA_NAME, @INDEX_NAME)) > 0

EXEC('ALTER INDEX [INDEX_NAME] ON ' + @SCHEMA_NAME + ' DISABLE');
--or

IF (SELECT DBO.INDEXEXISTSONSCHEMA(@SCHEMA_NAME,@INDEX_NAME)) > 0

EXEC('ALTER INDEX [INDEX_NAME] ON ' + @SCHEMA_NAME+ ' REBUILD');