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. I currently have a database that gets updated from a legacy application.

sql server - SQL Service Broker and .NET Windows Service - Best practices

I'd like to utilize a SQL Service Broker queue so that when a record is updated, a message is placed in the queue (using a trigger or something). I would then like to have a long running application (Windows service written in .NET) that is constantly "listening" to the queue to grab the messages and process them for another application. I've found some sample code on the web, and just wanted to get some input on whether the code is solid or not. So, here's an abbreviated version of the Windows service class: Sql - Tinyint vs Bit. Current community your communities Sign up or log in to customize your list. more stack exchange communities Stack Exchange sign up log in tour help.

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. Shared file databases may not be as well suited as client-server databases for very high-demand, production-quality Web applications. 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. SSMS Tools Pack Features. SSMS Tools Pack by Mladen Prajdić Features Download Licensing.

SSMS Tools Pack Features

How to export data from SQL Server 2008.2010 in DML (SQL script) Current community your communities Sign up or log in to customize your list. more stack exchange communities Stack Exchange sign up log in tour help careers 2.0 Stack Overflow.

How to export data from SQL Server 2008.2010 in DML (SQL script)

Query - How to read a Text file using T-SQL. Current community your communities Sign up or log in to customize your list. more stack exchange communities. Sql server - T-SQL: Opposite to string concatenation - how to split string into multiple records. You can also achieve this effect using XML, as seen here, which removes the limitation of the answers provided which all seem to include recursion in some fashion. The particular use I've made here allows for up to a 32-character delimiter, but that could be increased however large it needs to be. Then you can invoke it using: SELECT * FROM dbo.Split(' ', 'I hate bunnies')

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. 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. 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. 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. Declare a variable list in SQL Server stored procedure. Current community your communities Sign up or log in to customize your list. 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.. Slow in the Application, Fast in SSMS? Development content from SQL Server Pro. 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. DB2 Universal Database. Sql server - 900 byte index size limit in character length. The storage size for varchar is the actual length of the data entered + 2 bytes. Even though the column itself has that 2-byte overhead, you can put up to 900 byte varchar values into a column which is indexed. In practice, you can create an index on a column larger than 900 bytes in size, but you will have a problem if you actually try to insert something larger than 900 bytes: SQL Server CREATE TABLE syntax diagrams.

Sql - Are there any disadvantages to always using nvarchar(MAX) I know this is an old post, but I feel like it is not resolved - there is a lot of talk here about business logic and (n)varchar versus (n)text, but I feel like the original question had more to do with any negative performance implications of using (n)varchar(max) arbitrarily. At least, that is what I was searching for when I stumbled upon this post, and that is how I interpreted stucampbell's question. More specifically, I am interested in, if you expect the content to be under 8000 chars, but you don't know how long (and please, no rants about knowing the input - say this is a user comment field, or a chunk of html in a cms), is it more efficient to try to limit the field to an expected maximum (which you can then enforce in BL), or does it not make a difference?

Now, my (very) limited understanding (and this is really based on my ancient learning of SQL 6 and 7, so may be totally irrelevant!) Maximum Capacity Specifications for SQL Server. 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. SQL String User Function Workbench: part 2.

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. User Defined Functions in Microsoft SQL Server 2000. Export data from SQL Server to MS Access. Statistics in SQL Server. Named Pipe in ADODB Connection. How to Connect to a SQL Server Named Instance. 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.