background preloader

SQL Server Backup, Integrity Check, Index and Statistics Maintenance

SQL Server Backup, Integrity Check, Index and Statistics Maintenance

TSQL String Array Workbench /* Robyn and Phil show how to use XML-based arrays to make string handling easier in SQL Server 2005/2008, and illustrate the techniques with some useful functions*/ /*Arrays aren't difficult in SQL Server 2005. Here's a very simple technique that can be extended to do some remarkably complex string processing. A while back, a friend was bemoaning the poor string handling of SQL Server. It got us thinking. Take the PHP example...// Provides: You should eat pizza, beer, and ice cream every day$phrase = "You should eat fruits, vegetables, and fiber every day." $newphrase = str_replace($healthy, $yummy, $phrase); Let's convert this to its SQL Server equivalent...*/-- Provides: You should eat pizza, beer, and ice cream every dayDECLARE @phrase VARCHAR(MAX), @Healthy XML, @yummy XML, @newPhrase VARCHAR(MAX)SELECT @phrase = 'You should eat fruits, vegetables, and fiber every day --The Array() function Here is a simple function that turns a list into an XML fragment. --The str_Find function

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. */ This workbench finishes of what has been a three-part series of string functions. In it, we introduce the idea of using XML to provide a very simple array for doing string handling. This allows us to use functions for searching and splitting strings that will be familiar to users of procedural languages such as PHP and Python. The first part, TSQL String Array Workbench showed how the basics worked and then demonstrated how it could be used with a PHP-style string function. Of course, this principle could be extended to arrays and matrices. We then got rather diverted by the Python string functions (now string methods) and so wrote the SQL String User Function Workbench: part 1 that emulated all the python string functions that didn't have lists as parameters so we didn't use the XML array mechanism. p.s.

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... IF EXISTS ( SELECT 1 FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo]. CREATE FUNCTION [dbo]. DECLARE @encodedString VARCHAR(4000) IF @decodedString LIKE '%[^a-zA-Z0-9*-.! WHERE num BETWEEN 1 AND LEN(@decodedString) AND SUBSTRING(@decodedString,num,1) like '[^a-zA-Z0-9*-.! ENDELSEBEGIN SELECT @encodedString = @decodedString END RETURN @encodedString

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. 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. SQL SERVER 2005 has new priority HIGH as well as numeric-priority. Like this: Like Loading...

Using Large CLR UDTs in SQL Server 2008 | 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. CLR UDTs A little refresher course on CLR UDTs might be helpful as well. CLR UDTs aren’t intended to turn SQL Server into a full-blown object-oriented database. An easy way to understand the potential power of a CLR UDT is to examine how to use the built-in XML data type. Developing a CLR UDT The VarBinaryComp UDT Let’s now look at the VarBinaryComp UDT inside Visual Studio 2005. Compile and Deploy VarBinaryComp

Using Multiple Active Result Sets (MARS) SQL Server 2005 introduced support for multiple active result sets (MARS) in applications accessing the Database Engine. 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.

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. 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. Date and time types in SQL Server Prior to SQL Server 2008, there are two types in SQL Server to handle date and time. Note that there is no type to store only date or only time. SELECT CAST('20041223' AS datetime) ----------------------- 2004-12-23 00:00:00.000 As you can see, we have finally a date-only and a time-only type. What?

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 When I read various forums about SQL Server, I frequently see questions from deeply mystified posters. No, SQL Server is not about magic. To understand how to address that performance problem in your application, you need to read on. Table of Contents Presumptions The essence of this article applies to all versions of SQL Server, but the focus is on SQL 2005 and later versions. For the examples in this article I use the Northwind sample database. This is not a beginner-level article, but I assume that the reader has a working experience of SQL programming. Caveat: In some places I give links to the online version of Books Online. How SQL Server Compiles a Stored Procedure In this chapter we will look at how SQL Server compiles a stored procedure and uses the plan cache. What is a Stored Procedure? Stored procedures. Overview

sql server - SQL Service Broker and .NET Windows Service - Best practices SQL-Viewer - Project Web Hosting - Open Source Software Remote MySQL Viewer - Project Web Hosting - Open Source Software BasicQuery SourceForge Project Homepage SQL Buddy Sourceforge Site c# - SQL: Update a row and returning a column value with 1 query