Posts

SQL 2008

SQL Server 2008, the next release of Microsoft SQL Server, provides a comprehensive data platform that is more secure, reliable, manageable and scalable for your mission critical applications, while enabling developers to create new applications that can store and consume any type of data on any device, and enabling all your users to make informed decisions with relevant insights. more

Microsoft a BI Force

Image
Microsoft BI is a new tool. It is delivered through three
layers or workloads: data warehousing, reporting and
analysis, and performance management. These are
designed to provide a consolidated, comprehensive data
source and tools to help improve decision making.
Microsoft BI is to help decision makers at all levels.
An IDC report issued this summer—found that Microsoft’s
BI market share growth outpaced that of most rivals.
More to the point, both Gartner and IDC now agree:
Microsoft is now a BI force to be reckoned with, trailing
established leaders such as Business Objects SA, SAS
Institute Inc., and Cognos Inc. for overall BI market
laurels. more

ARRAY In SQL Server 2000

Image
On a number of occasions that I have regretted the fact that there is no such thing as Array in Microsoft SQL Server's Transact-SQL. Lots of other SQL programmers would mention the same thing. The array is one of the most common and highly used programming structures. Indeed, T-SQL does not provide for the fully functional array structure. However, SQL 2000 introduced the new feature called a variable of type table, which allows for mimicking an array and/or using it instead of SQL Server cursor.

It can be a challenge to pass such a list of values to a stored procedure. In earlier times I had been using so called composite queries, or dynamic SQL. It includes putting together a string to be compiled and executed using EXECUTE statement. For example:

CREATE PROCEDURE CustomerByRegion
@List varchar(100)
AS
declare @sql varchar(1000)

set @sql = 'select * from Customers where Region IN (' + @List + ')'

execute (@sql)

-- call procedure
declare @List varchar(100)
set @List = …

Data Import Functionality Using SQL Server

Image
DTS (Data Transformation Services) in SQL Server is very powerful feature and can be used to solve many database related issues starting from data migration, conversion and import/export. Even content publication/management applications can be developed easily using DTS. SQL Server’s DTS support is extended with latest version of SQL Server 2000. This article’s main goal is not only to expose the DTS functionality of SQL Server but also to show the simplicity in using DTS packages to implement core functionality.
This case study takes up a sample financial web application “Trade Analysis” designed and developed by Prakash Financials. Trade Analysis application supports trade analysis for client specific trade data. Client in this article represents mostly an organization. ‘User’ is any user that uses the services of “Trade Analysis”. There may be many users from one single organization/client and there may be more than one client registered for the using “Trade Analysis”.…

Embeded Database

Image
VistaDB 2.1 database for .NET has been released
This 2.1 update includes over 60 improvements, including new support for .NET 2.0 and Visual Studio 2005. VistaDB is a small-footprint, embedded SQL database alternative to Jet/Access, MSDE and SQL Server Express 2005 that enables developers to build .NET 1.1 and .NET 2.0 applications. Features SQL-92 support, small 500KB embedded footprint, free 2-User VistaDB Server for remote TCP/IP data access, royalty free distribution for both embedded and server, Copy 'n Go! deployment, managed ADO.NET Provider, data management and data migration tools. Free trial is available for download.
- Learn more about VistaDB
- Repost this to your blog and receive a FREE copy of VistaDB 2.1!

Parsing CSV Values Into Multiple Rows

Image
This article explains how to take a string of comma-separated values
(CSV), and parse out the values individually. If you have a table with
a column of CSV values, and you want to translate them into a normalized
table, it can really difficult. You would need to use a cursor to loop
through your CSV table and process each row. Fortunately there's a
way to do this using just a regular SELECT. It requires a table of numeric
values, but it does the job beautifully; you can INSERT normalized
CSV values into a table with one SQL statement. The tricky part is
that each word can be a different length, and each CSV can have a
different number of words in it. We'll need a separate table (Tally),
with a single column of sequential integer values. The "tally table" is
mentioned in Joe Celko's SQL for Smarties -- the best SQL book ever
written -- as a handy source for a numeric sequence. Here, it will
replace the loop needed to parse out each value from the CSV string.


SELECT Author,
NullI…

Understanding DBCC SHOWCONTIG

Image
DBCC SHOWCONTIG is a wonderful tool which helps you to understand
quite a bit more about your system than is obvious at first glance. And,
frankly, the documentation doesn't use terminology that makes it very
obvious either. So, this article will focus on a few of the big ideas behind
the tool using SQL Server, and how you can use it to better understand
what is going on inside your SQL Server box. Probably one of the most
significant performance problems found in databases is centered around
table data fragmentation. One situation that may be analogous to table
fragmentation might be an index at the end of a large book. A single index
entry in such a book might point to several pages scattered throughout
the book. You must then scan each page for the specific information you
require. This differs significantly from the index of the phone book which
stores its data in sorted order. A typical query for the name "Jones"
might span multiple consecutive pages, but are always held in a so…