How to generate a RNGCryptoServiceProvider-like number in TSQL
Does anyone know of a way to generate a RNGCryptoServiceProvider-like random number in T-SQL without having to register any .NET Assemblies? ...
tsql
SQL Server - Storing linebreaks in XML data type
Is it possible to store non-alphanumeric characters (more specifically line break characters) in a XML data type? The code below illustrates my problem: declare @a xml declare @b nvarchar(max) set @b = '<Entry Attrib="1'+CHAR(13)+'2" />' print @b set @a=convert(xml,@b,1) set @b=convert(nvarchar, @a,1) print @b The output is: <E...
What is the fastest way to delete massive numbers of records in SQL?
Possible Duplicate: What is the best way to delete a large number of records in t-sql? What is the fastest way to delete massive numbers (billions) of records in SQL? I want to delete all the records that match a simple rule like myFlag = 3. Is it possible to add a WHERE clause to a TRUNCATE? Another solution that could be po...
Is WHERE ID IN (1, 2, 3, 4, 5, ...) the most efficient?
I know that this topic has been beaten to death, but it seems that many articles on the Internet are often looking for the most elegant way instead of the most efficient way how to solve it. Here is the problem. We are building an application where one of the common database querys will involve manipulation (SELECT’s and UPDATE’s) based ...
TSQL: string operations to match a pattern or wildcard for one character
Consider the need to query for a certain pattern of data within a column. The example I'll use are customers with Canadian postal codes. ID Postal -- ------- 442 90210 631 T0R 4C2 447 YO31 1EB 145 F9S8S6 73 K9J 3K3 Pretend you don't have an easy out (like a state/prov or country field), or that...
Pivoting in T-SQL based on date offset
There is a table called Orders has three columns: OrderID UserID OrderDate I need to know, for X months in the past, A users placed 1 order, B users placed 2 orders, C users placed 3 orders, etc. So the output is a table w/ a MinDate field and then columns for each # of orders from 1 to N, e.g.: declare @t table ( MinDate dateti...
Caching aggregate results in Linq2SQL
We have a small affiliate program and a simple tool to view affiliate statistics. The Linq for showing the report: from e in linq0 select new { Id = e.Id, Name = e.CompanyName, EnquiryCount = (int?)e.CampaignCodes.Sum(f => f.Enquiries.Count()) ?? 0, EnquiryOrderSum = (int?)e.CampaignCodes.Sum(f => (int?)f.Enquiries.Sum...
What effect does HOLDLOCK have on UPDLOCK?
I have seen many examples of the HOLDLOCK hint being used in combination with UPDLOCK (like this). However as I read the documentation for these hints, it seems that HOLDLOCK should be redundant, since UPDLOCK already persists the lock until the end of the transaction. (Also it seems to say that HOLDLOCK only applies to shared locks anyw...
Determine a table's primary key using TSQL
I'd like to determine the primary key of a table using TSQL (stored procedure or system table is fine). Is there such a mechanism in SQL Server (2005 or 2008)? ...
Compare with Dates and ID?
Using SQL Server 2000 I want to get Table2.TimeIn Table2.TimeOut according to Table1.personid and also If Table1.Date = Table3.Date then it should take a Table3.TimeIn, Table3.TimeOut. 3 Tables Table1 ID Date 001 20090503 001 20090504 001 20090506 002 20090505 002 20090506 So on…, Table2 ID TimeIn TimeOut 001 08:00:...
sql Server error - 3709
Hi I keep getting the error: Error (3709) - /mysite/Pages_Secure/mypage.asp ADODB.Recordset. "The connection cannot be used to perform this operation. It is either closed or invalid in this context.." strQuery = "" strQuery = strQuery + "SET ROWCOUNT 0 " strQuery = strQuery + "SELECT FIRSTNAME, LASTNAME, EMAIL, USER_TEAM_ID, USER_SERV...
SQL Server Table and Column Alias for legacy applications
Having just started working for a new client I have noticed that their database schema could do with a serious overhaul. I've made some suggestions (naming conventions mainly) which would be acceptable for the new suite of applications we are developing going forward, however the system would also have to support the lagacy names used ...
How to get first day of the year with SQL?
Hello, I'm working on a purging procedure on SQL Server 2005 which has to delete all rows in a table older than 1 year ago + time passed the current year. Ex: If I execute the procedure today 6-10-2009 it has to delete rows older than 2008-01-01 00:00 (that is 2007 included and backwards). How can I get the date of the first second ...
Compare with Date and ID problem
Table1 ID Date Intime Outtime A001 20000501 12:00:00 22:00:00 A001 20000502 14:00:00 22:00:00 A001 20000503 12:00:00 23:00:00 A002 20000501 11:00:00 20:00:00 A002 20000502 13:00:00 21:00:00 So on…, Table2 ID Date Intime Outtime A001 20050501 14:00:00 23:00:00 A002 20050501 08:00:00 16:00:00 From the above table I want...
SQL Server: Inline Table-Value UDF vs. Inline View
I'm working with a medical-record system that stores data in a construct that resembles a spreadsheet--date/time in column headers, measurements (e.g. physician name, Rh, blood type) in first column of each row, and a value in the intersecting cell. Reports that are based on this construct often require 10 or more of these measures to b...
SQL IsNumeric(), shouldn't it return a bit?
I'm just curious to know why SQL IsNumeric() returns int instead of bit? Seems like it should return a bit. ...
How to mimick Oracle Materialized Views on MS SQL Server?
Application connected to MS SQL Server will create views where a single row result is an analysis including aggregations of 1-10k records. The applicable criteria across the resulting view will have dozens to tens of thousands of results. The view+criteria will then be ordered by some column (user specified) in the view which are most li...
How do SQL Server View Indexes work under the hood?
How do SQL Server View Indexes work under the hood? I've read this TechNet article but it doesn't explain how a view index actually works. Can anyone explain this to me? Note: I'm not sure if this should have gone on SF. If it should, just move it over there. ...
linked-server sql - access Problem
Hi, I have a SQL server 2000 and an Access database mdb connected by Linked server on the other hand I have a program in c # that updates data in a SQL table (Users) based data base access. When running my program returns the following error message: OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed. [...
SQL Aggregation for a smaller result set
I have a database for which I need to aggregate records into another smaller set. This result set should contain the difference between maximum and minumum of specific columns of the original records where they add up to certain SUM, a closed interval constant C. The constant C determines how the original records are aggregated and no ...