Navigation for dotNetTips.com - SQL Server

As you might know I'm really picky when it comes to formatting code... heck, I event wrote a book on it! This even extends to T-SQL. I have worked at companies that had stored procedures that were formatted so poorly that I could not follow it at all. Thankfully I found a very cool web site called Instant SQL Formatter that does all the work for me... instantally! It will format T-SQL like this (generated by SQL Server Management Studio):

USE [Acme]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].EventLog_DeleteOld') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].EventLog_DeleteOld
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].EventLog_DeleteOld

AS
BEGIN
 DELETE FROM EventLog WHERE ([TimeStamp] < GETDATE() - 14)
END
GO

To this:

USE [Acme]

GO

IF EXISTS (SELECT *
           FROM   sys.objects
           WHERE  object_id = Object_id(N'[dbo].EventLog_DeleteOld')
                  AND TYPE IN (N'P',N'PC'))
  DROP PROCEDURE [dbo].eventlog_deleteold

GO

SET ansi_nulls  ON

GO

SET quoted_identifier  ON

GO

CREATE PROCEDURE [dbo].Eventlog_deleteold
AS
  BEGIN
    DELETE FROM eventlog
    WHERE       ([TimeStamp] < Getdate() - 14)
  END

GO

Very nice! There are lots of options (the T-SQL was formatted with just the defaults).

Tip Submitted By: David McCarter

The SQL Server 2005 Best Practices Analyzer (BPA) gathers data from Microsoft Windows and SQL Server configuration settings. BPA uses a predefined list of SQL Server 2005 recommendations and best practices to determine if there are potential issues in the database environment.

http://www.microsoft.com/downloads/details.aspx?FamilyID=DA0531E4-E94C-4991-82FA-F0E3FBD05E63&displaylang=en

The link below takes you to the Microsoft Express products like Visual Basic Express 2008 and SQL Server Express 2008.

http://www.microsoft.com/express/

REDMOND, Wash. — Aug. 6, 2008 — Microsoft Corp. today announced the release to manufacturing of Microsoft SQL Server 2008, the new version of the company’s acclaimed data management and business intelligence platform. This version of SQL Server provides powerful new capabilities such as support for policy-based management, auditing, large-scale data warehousing, geospatial data, and advanced reporting and analysis services. SQL Server 2008 provides the trusted, productive and intelligent platform necessary for business-critical applications.

“Microsoft developed this release of SQL Server with the customer in mind,” said Ted Kummert, corporate vice president of the Data and Storage Platform Division at Microsoft. “SQL Server 2008 is the only major database that includes comprehensive, tightly integrated functionality for data management as well as advanced business intelligence out of the box. By offering a complete solution, we save customers time and money and allow them to focus on deriving the most value from their data assets.”

With more than 450,000 customer and partner downloads of SQL Server 2008’s community technology previews (CTPs), more than 75 large-scale applications already in production and more than 1,350 applications being developed by nearly 1,000 independent software vendors (ISVs) on SQL Server 2008, it’s clear that customers and partners are excited about the capabilities now available in SQL Server 2008. Tim Whitehorn, founder and chief executive officer of event management software provider ServiceU Corp., said, “We selected SQL Server 2008 because we can entrust it with our critical business applications. SQL Server 2008 is highly secure and reliable, and offers the best value on the market today. In addition, it accelerates the time frame for deploying new features and enhancements, and that to us makes SQL Server a winner.”

A number of enterprise customers from various industries are testing SQL Server 2008 including Clear Channel Communications Inc., Fidelity Investments, Hilton Hotels Corp., Simon & Schuster Inc., Siemens AG and Xerox Corp., among many others.

SQL Server has been well-received in the analyst community. Noted Forrester Research Inc. analyst Noel Yuhanna wrote in a January 2008 report, “The Forrester Wave: Information-As-A-Service, Q1 2008,” “Microsoft is (one of) the only vendor(s) with top enterprise search and business intelligence reporting capabilities. It offers strong support for aggregation, summarization, search engine and dashboards. Transactions across distributed data sources and long-running transactions are also strengths for Microsoft.”

SQL Server is an industry leader in both scalability and performance. It is the first and only database management system to be proven capable of delivering scalable results on TPC-E, the Transaction Processing Performance Council’s (TPC) newest and most challenging online transaction processing (OLTP) benchmark, with 13 published benchmarks to date.¹ Seven benchmarks are published on Windows Server 2008 and SQL Server 2008 by multiple hardware vendors, demonstrating strong price-performance and performance results, including IBM Corp.’s new No. 1 performance result using SQL Server 2008 on a 64-core System x3950 M2 server.² Also demonstrating the performance of SQL Server 2008, Unisys Corp. and Microsoft set a new extract, transform and load (ETL) performance record by loading 1 terabyte of data in less than 30 minutes using SQL Server 2008 Integration Services. In addition, SQL Server 2008 has proven its scalability in large-scale data warehousing, as demonstrated by its recent 10-terabyte TPC-H benchmark.³

“We saw a 35 percent improvement in throughput on the system that we upgraded to SQL Server 2008, with no code changes on our end,” said Gary Oberg, vice president of IT and development at Applied Discovery (a member of the LexisNexis group). “This translates straight to the bottom line for us, as the more documents we can upload, the more we can process.”

Microsoft is setting additional benchmark records with ISV solutions, including a world record on four-socket industry-standard blade servers in a three-tier SAP Sales and Distribution (SD) Standard Application Benchmark, and demonstrated the largest benchmark ever on the Siemens Teamcenter digital product life-cycle management solution, Microsoft Dynamics CRM, Microsoft Dynamics AX and the Camstar Manufacturing Execution System solution.

SQL Server is a key component of the Microsoft Application Platform, a suite of products and technologies designed to help customers build, run and manage dynamic business applications. SQL Server 2008 is available in the following editions:

Pricing and Availability

SQL Server 2008 is now available to MSDN and TechNet subscribers and will be available for evaluation download on Aug. 7, 2008. SQL Server 2008 Express and SQL Server Compact editions are available for free download today at http://www.microsoft.com/sqlserver. As previously announced, pricing for SQL Server will not increase with SQL Server 2008. More information is available at http://www.microsoft.com/presspass/presskits/sqlserver.

I found out the hard way in the last two days that an SQL Server CE database does not work properly when creating a typed Dataset with a ASP.NET 3.5 application. Sure, you can create one in the IDE, but the code-on-the-fly will not be generated therefor you can't use it in your code. Actually causes an error if you try to build.

Rec'd an confirmation from Microsoft today that they did not implement this for ASP.NET. Just wanted to warn you so you don't waste an hour plus like I did. If you still want to use SQL Server CE and typed DataSets in ASP.NET you will need to create a separate assembly (which should be done anyway). Oh, won't work with LINQ either.