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 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.

Versions of SQL Server This article applies to SQL Server 7.0, 2000, 2005 and 2008, where not noted otherwise. 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. I was perusing the newsgroups when I came across a request in the microsoft.public.sqlserver.clr newsgroup to see if there was a way to use System.Web.HttpUtility.UrlEncode in T-SQL.

Peter DeBetta's SQL Programming Blog : T-SQL UrlEncode

I know using the CLR is an option, but I decided to write a T-SQL equivalent. I reflected the code to figure out the exact rules for UrlEncoding. Then, I discovered much to my delight, a new system function in SQL Server 2005 that converts a string to its hexadecimal equivalent as a string– sys.fn_varbintohexstr. I knew there was an extended stored procedure in prior versions of SQL Server to do this conversion, but this function made writing this UrlEncode function even easier. CREATE FUNCTION dbo.UrlEncode(@url NVARCHAR(1024))RETURNS NVARCHAR(3072)AS BEGIN DECLARE @count INT, @c NCHAR(1), @i INT, @urlReturn NVARCHAR(3072) SET @count = LEN(@url) SET @i = 1 SET @urlReturn = '' WHILE (@i <= @count) BEGIN SET @c = SUBSTRING(@url, @i, 1) IF @c LIKE N'[A-Za-z0-9()''*\-._!

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. Translations provided by SQL Server MVP Frank Kalis. Introduction. SSMS Tools Pack Features. SSMS Tools Pack.

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. 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. Egad, a technical post! And after I'd already said goodbye for the month. A long time ago, a friend taught me that expectations are small murders. What does that make plans? At any rate, Peter asked a FAQ on one of the internal discussions this morning, and since we haven't discussed it here.. I have a simple question regarding the Select Top …. clause.When I use this clause, will the result be always the same, or does SQL 2000 (in my case here) return always a different set of data?

Bruno replied: If you don’t define an “order by”, by standard SQL semantics, you are not guaranteed to always have the same result. .. which led me to post the following: Just to expand on Bruno’s point a bit.. Slow in the Application, Fast in SSMS? An SQL text by Erland Sommarskog, SQL Server MVP. 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). 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. 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. 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. Clustered Index and Non-Clustered Index in SQL Server. 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.

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]. 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. What happened and what do I need to tell them to fix the issue? Solution. Timeout error when trying to delete records. Overview of the SQL Server Browser service. Andy Leonard : Quick Database Connectivity Testing. Named Pipes Provider, error: 40 - Could not open a connection to SQL Server - SQL Protocols. 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.