background preloader


Facebook Twitter

Using Multiple Active Result Sets (MARS) SQL Server 2005 introduced support for multiple active result sets (MARS) in applications accessing the Database Engine.

Using Multiple Active Result Sets (MARS)

In earlier versions of SQL Server, database applications could not maintain multiple active statements on a connection. When using SQL Server default result sets, the application had to process or cancel all result sets from one batch before it could execute any other batch on that connection. SQL Server 2005 introduced a new connection attribute that allows applications to have more than one pending request per connection, and in particular, to have more than one active default result set per connection. MARS simplifies application design with the following new capabilities: Applications can have multiple default result sets open and can interleave reading from them. Applications using MARS will find the following guidelines beneficial: SQL Server Native Client does not limit the number of active statements on a connection.

SQL Server Express Edition. Things you can do Build web and mobile applications for multiple data types Support structured and unstructured data while storing business data with native support for relational data, XML, and spatial data.

SQL Server Express Edition

Add geographical information to business applications and build location-aware applications. Increase granularity of temporal data with date and time data types. Tibor Karaszi's SQL Server pages. Overview The purpose of this article is to explain how the datetime types work in SQL Server, including common pitfalls and general recommendations.

Tibor Karaszi's SQL Server pages

Thanks to SQL Server MVP Frank Kalis, this article is translated to German. There is also a Russian version, thanks to Evgeny Vinchyk from Ukraine, available as a word document. Acknowledgements I like to thank the following persons who provided valuable suggestions and input for this article: Steve Kass, Aaron Bertrand, Jacco Schalkwijk, Klaus Oberdalhoff, Hugo Kornelis, Dan Guzman and Erland Sommarskog.

Sql server - SQL Service Broker and .NET Windows Service - Best practices. Sql - Tinyint vs Bit. Accessing Data with ADO. For performance and reliability reasons, it is strongly recommended that you use a client-server database engine for the deployment of data driven Web applications that require high-demand access from more than approximately 10 concurrent users.

Accessing Data with ADO

Although ADO works with any OLE DB compliant data source, it has been extensively tested and is designed to work with client server databases such as SQL Server. ASP supports shared file databases (Access or FoxPro) as valid data sources. Although some ASP example code use a shared file database, it is recommended that these types of database engines be used only for development purposes or limited deployment scenarios.

Peter DeBetta's SQL Programming Blog : T-SQL UrlEncode. The curse and blessings of dynamic SQL. An SQL text by Erland Sommarskog, SQL Server MVP.

The curse and blessings of dynamic SQL

Latest revision: 2011-06-23. An earlier version of this article is also available in German. SSMS Tools Pack Features. How to export data from SQL Server 2008.2010 in DML (SQL script) Query - How to read a Text file using T-SQL. Sql server - T-SQL: Opposite to string concatenation - how to split string into multiple records. Peter DeBetta's SQL Programming Blog : T-SQL UrlDecode. INSERT (Transact-SQL) WITH <common_table_expression> Specifies the temporary named result set, also known as common table expression, defined within the scope of the INSERT statement.

INSERT (Transact-SQL)

The result set is derived from a SELECT statement. For more information, see WITH common_table_expression (Transact-SQL). TOP (expression) [ PERCENT ] Specifies the number or percent of random rows that will be inserted. expression can be either a number or a percent of the rows. Is an optional keyword that can be used between INSERT and the target table. SELECT @local_variable (Transact-SQL)

Specifies that the specified local variable that is created by using DECLARE @local_variable should be set to the specified expression.

SELECT @local_variable (Transact-SQL)

For assigning variables, we recommend that you use SET @local_variable instead of SELECT @local_variable. For more information, see SET @local_variable. Storing Variable Values in Temporary Array or Temporary List. SQL Server does not support arrays or a dynamic length storage mechanism like list.

Storing Variable Values in Temporary Array or Temporary List

Absolutely there are some clever workarounds and few extra-ordinary solutions but everybody can;t come up with such solution. Additionally, sometime the requirements are very simple that doing extraordinary coding is not required. Here is the simple case. Let us say here are the values: a, 10, 20, c, 30, d. Now the requirement is to store them in a array or list. Declare a variable list in SQL Server stored procedure. Database Programming: TOP Without ORDER BY? - Ward Pond's SQL Server blog. Slow in the Application, Fast in SSMS? An SQL text by Erland Sommarskog, SQL Server MVP.

Slow in the Application, Fast in SSMS?

Last revision: 2013-08-30. This article is also available in Russian, translated by Dima Piliugin. Introduction. Development content from SQL Server Pro. New in SQL Server 2008 is support for large user-defined types (UDTs).

Development content from SQL Server Pro

We’re going to build a Common Language Runtime (CLR) UDT that allows us to store, compress, and decompress binary data (up to 2GB in size) inside SQL Server 2008. This technique works only in SQL Server 2008 because only SQL Server 2008 allows CLR UDTs larger than 8KB. The UDT we’ll build, called VarBinaryComp, accepts both uncompressed data (which it will compress before storing) and compressed data as input. It also has properties to determine the length of the compressed and uncompressed data. To implement this UDT, we’ll leverage the compression/decompression logic covered in “Zip Your Data,” InstantDoc ID 49065.

CLR UDTs A little refresher course on CLR UDTs might be helpful as well. MD5 Hash SQL Server Extended Stored Procedure. Fix : Error 1205 : Transaction (Process ID) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction. Fix : Error 1205 : Transaction (Process ID) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction. Deadlock occurs when two users have locks on separate objects and each user wants a lock on the other’s object. When this happens, SQL Server ends the deadlock by automatically choosing one and aborting the process, allowing the other process to continue. The aborted transaction is rolled back and an error message is sent to the user of the aborted process.

Generally, the transaction that requires the least amount of overhead to rollback is the transaction that is aborted. Fix/Workaround/Solution: Deadlock priority can be set by user. To reduce the chance of a deadlock: Minimize the size of transaction and transaction times. DB2 Universal Database. Sql server - 900 byte index size limit in character length. SQL Server CREATE TABLE syntax diagrams. Sql - Are there any disadvantages to always using nvarchar(MAX) Maximum Capacity Specifications for SQL Server. The following table specifies the maximum sizes and numbers of various objects defined in SQL Server databases or referenced in Transact-SQL statements. 1Network Packet Size is the size of the tabular data stream (TDS) packets used to communicate between applications and the relational Database Engine. The default packet size is 4 KB, and is controlled by the network packet size configuration option. 2The maximum number of bytes in any index key cannot exceed 900 in SQL Server.

You can define a key using variable-length columns whose maximum sizes add up to more than 900, provided no row is ever inserted with more than 900 bytes of data in those columns. In SQL Server, you can include nonkey columns in a nonclustered index to avoid the maximum index key size of 900 bytes. 3Database objects include objects such as tables, views, stored procedures, user-defined functions, triggers, rules, defaults, and constraints. 5This value is for static lock allocation. Data Access content from SQL Server Pro. Defining indexes on sort columns can yield dramatic performance results The right index can dramatically improve SQL Server's sort performance. Defining a clustered index on a sort column, for example, forces the database to store data in sorted order, letting you retrieve data without additional sorting. Note that SQL Server 7.0 and earlier releases let you create indexes only in ascending order, so if your query requires data in descending order, you'll probably have to perform additional sorting and use internal worktables to get data in the right order.

Clustered Index and Non-Clustered Index in SQL Server. Here we are going to discuss clustered and non-clustered indexes in SQL Server; to understand these indexes clearly we should first know what database indexes are. So let's have a look at database indexes. Database indexes are copies of your data in a table that is sorted a certain way. Database administrators can gain potential performance benefits through the judicious use of indexes on database tables. Indexes allow you to speed query performance on commonly used columns and improve the overall processing speed of your database. Immediate deadlock notifications without changing existing code. In my previous post about immediate deadlock notifications in SQL Server 2005 I've shown a way to use a try catch block to get the deadlock error. The con of this method, although it uses best practice for error handling in SQL Server 2005, is that you have to change existing code and it doesn't work for non stored procedure code.

And that IS a pretty BIG con! As is customary in this blog there is a solution to this. :) SQL String User Function Workbench: part 2. /* In which Robyn and Phil pull together the themes from their TSQL String Array Workbench and String User Function workbench, to provide a simple TSQL string-handling package.

TSQL String Array Workbench. SQL Server Backup, Integrity Check, Index and Statistics Maintenance. SQL-Viewer - Project Web Hosting - Open Source Software. Remote MySQL Viewer - Project Web Hosting - Open Source Software. BasicQuery SourceForge Project Homepage. T-SQL Developer project home page. SQL Buddy Sourceforge Site. Free Development software downloads. C# - SQL: Update a row and returning a column value with 1 query.

LINQ to XSD - Home. URLEncode. Hello Robert Cary, Thank you for the cool TSQL URLEncode script, it was almost exactly what I was looking for. I slightly modified it to allieviate the need for creating/populating table numbers by selecting from a derived table using RowNum() as the incremented field [num] Alzowze ";0)ps... You wouldn't have a TSQL Is_UTF8 Function in your box of tricks would ya? IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo]. CREATE FUNCTION [dbo]. DECLARE @encodedString VARCHAR(4000) User Defined Functions in Microsoft SQL Server 2000.

Export data from SQL Server to MS Access. Introduction. Statistics in SQL Server. Named Pipe in ADODB Connection. How to Connect to a SQL Server Named Instance. Problem My network admins have tightened down the network and while we were once able to connect to our named instances via MySQLServer\MyNamedInstance, we're now only able to connect if we specify the port. Timeout error when trying to delete records. Overview of the SQL Server Browser service. Problem. Andy Leonard : Quick Database Connectivity Testing. Introduction Ever find yourself needing to test connectivity, but you’re on a machine with no client tools?

It happens to me occasionally. Here’s one trick to help: UDL files Right-click on the Windows Desktop, hover over New, and click Text Document: Rename the file to test.udl: Because you are changing the extension, you will be prompted: Click Yes. Click the Test Connection button to test connectivity: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server - SQL Protocols. This error was most frequently hitted by our customers, and in this post, give a brief summary of troubleshooting tips for this specific error message. First, take a look at below MSDN forum link lists about this topic: Sql server - How to get a list of users for all instance's databases. SQL Server Named Pipe Connection... Asp classic - Server-side Javascript in production fails to open connection to a named instance of SQL2008. How To Set the SQL Server Network Library in an ADO Connection String.

Aaron Bertrand : Bad habits to kick : choosing the wrong data type. Chastised for recommending use of bit data type. SQL Fiddle. CONQUEST SOFTWARE SOLUTIONS - Tools for Oracle development, database administration, SQL and PL/SQL formatting, analysis and flowcharting, oracle development tools, pl/sql gui, plsql ide, pl/sql tools, pl/sql ide, pl/sql environment, pl/sql development, p. SQL Developers - Mimer SQL-2003 Validator. Subqueries with NOT EXISTS. Sys.objects (Transact-SQL) Meta Data Functions. IF...ELSE. Sql server - How do you perform an IF...THEN in an SQL SELECT. CAST and CONVERT. Sql server 2000 - SQL2000 safely cast a VARCHAR(256) to INT. Datetime (Transact-SQL) Int, bigint, smallint, and tinyint. Decimal and numeric. Float and real. Data Types. Ntext, text, and image.