Questions for tsql



I have a table that contains an orderid, an inventoryid, and a quantity -- its a line items table. The database is SQL Server 2008.

SQL Statement to get contract renewal amount by time range

I have two views. One view shows me the expiration dates of contracts of a customer. The other one is returning all the start dates of the customer's contracts. I wan't to find out the renewal amou...

Join on tables with OR (repeating data) - SQL Server 2005

Firstly to put things into context I am trying to write a search on SQL Server 2005. Below is my table structure

formatting sql server PRINT messages?

I have the following Print statement which print some details but it is not tabbed properly and looks awful. is there any way i could print them in tabbed format so they all meet at the same place ...

How to refer to Excel columns without headers in TSQL

I dived into documentation but since I didn't find any information (Well, actually I found similarities with some other SO questions but not what I want), I'm asking you guys to help me :

How do I get “select for xml” to output to several files?

Our customer is complaining that our export file is too long; they would like us to split the export into many files with no more than “n” records per file. Is there a way of doing this with “sele...

Can you have a WITH statement in a tabular user defined function

I have the following code for a UDF but it errors with the message:

Reduce deadlock on PAGE level on update query on MS SQL

I have some funny deadlock caused by a stupid simple SQL UPDATE query, on a flat plain table, under default "READ COMMITED" transaction.

Cursor loop is not working. Only taking last value in parameter?

I created stored proc so that user can select multiple comma separated values into single parameter.

Search Entire Database To find extended ascii codes in sql

We have issues with extended ascii codes getting in our database (128-155)

datatype supplied by user for SQL field

I have a SQL table which has a number of fields

Inner join on null id select row

We have two tables one with ids and one with names. Some id's are not required (are nullable) for example :

MERGE to target columns using source rows?

I have some nicely-structured data that looks like this:

TSQL A recursive update?

I'm wondering if exists a recursive update in tsql (CTE)

Setting and resetting the DATEFORMAT in SQLServer 2005

Is there a way to query the current DATEFOMRAT SQLServer 2005 is currently using with T-SQL?

T-SQL Transaction with many (1000+) INSERTS gets disconnected

Is there a limit on the number of INSERT statements you can put inside a transaction?

How to use JOIN in UPDATE query

I am very weak at join and i need to know that how can i convert following query using joins. This Query is working fine with sub queries but i need to convert it and use it making joins.

Input to SQL Stored Procedure

I am trying to grab a querystring from the URL and send it to my stored procedure in MSSQL. The querystring is of type varbinary and when i try to send it my application is throwing an exception. I...

Best way to periodically remove a set of records with LINQ to SQL

This is my first crack at a method that is run periodically during the lifetime of my ASP.NET application to clean up expired sessions stored in my database. It seems to work pretty well, but the

Can't execute a COMPUTE statement

I am trying to execute this simple statement on Northwind database

Avg on datetime in Access

I am porting some queries from Access to T-SQL and those who wrote the queries used the Avg aggregate function on datetime columns. This is not supported in T-SQL and I can understand why - it doe...

Search SQL Server metadata

I need to search SQL Server's metadata for all stored procedures that were changed between specific dates.

Getting number of common terms in two tables where there are duplicates

Consider the following two tables in SQL Server 2008:

T-SQL View type conversion error; DROP and CREATE view makes issue vanish?

This error is really strange (SQL Server 2008 R2 (RTM). I am SELECTing from a view in some application code, and very infrequently, I get an error about an invalid data type conversion. The SQL i...

Error converting data type varchar to numeric when casting varchar(max) to decimal

I'm trying to sum values from two strings. In order to do that I'm casting the strings in to decimals, add them up and then cast it back to string for the output. Also using max to avoid duplication

T-SQL Optimize DELETE of many records

I have a table that can grew to millions records (50 millions for example). On each 20 minutes records that are older then 20 minutes are deleted.

TSQL try catch transaction error handling, transaction count mismatch

I have a sproc that I am calling from C# with a transaction by doing:

Computed Column Formula and how it can work base on condition

I have this challenge. I want to achieve a formula in a computed column. Currently my formula is: ((((([sponsorID]+'

TSQL How to exec a stored procedure inside select query?

is it possible to execute a storep procedure inside select query ?

Obtaining Sums from Multiple Tables

I'm having an issue extracting Sums from multiple tables in a single SQL statement.

SQL: Remove duplicates (slightly different)

I asked a question similar to this one here and was given great answers, but it turns out the following way is the way it needs to be done. I've been working on this for several hours now and haven't

Include carriage returns when calculating substring

I have some data which is stored in a VARCHAR(MAX) column that contains the control characters CR & LF (CHAR(10) & CHAR(13)).

Creating table with the same columns as in a csv

I am writing a stored procedure which is supposed to take data from a csv file and insert into a table. My problem is that the number of columns in the csv file are not fixed(ie number of columns is

Does query plan optimizer works well with joined/filtered table-valued functions?

In SQLSERVER 2005, I'm using table-valued function as a convenient way to perform arbitrary aggregation on subset data from large table (passing date range or such parameters).

Do I need to use the dreaded sql server loop/ cursor for the result set I need?

I need a sql server result set that "breaks" on a column value, but if I order by this column in a ranking function, the order I really need is lost. This is best explained by example. The query I'm

SQL Server try-catch inner exception message conundrum

The following SQL statement below is a good example of some SQL that throws an exception with nested details. It seems in the catch part of the statement I can only get outer exception details Coul...

Is it possible to use CONTAINSTABLE to search for “word1” in column1 AND “word2” in column2

We used to have a search, that checks two columns for some words. Both columns must contain some words provided, so we use AND ... no doubts FULLTEXT INDEX is used on the columns.

SQL Server and CLR, batching SqlFunction

I have a CLR function that returns "n" rows with random data. For example, to prime an empty table with 100 rows of test data I could write

Understanding what the query is and does?

This is going to be a strange request, can someone explain in plain English what the query below is doing especially the <>0 bit.

T-SQL elegant solution to divide a numeric value to multiple accounts

I have a problem that I believe has a perfectly elegant solution, but would like some help.

how to mark records that are read in mssql

I have created a system that sends some information by SMS daily to our customers. as the procedure of sending SMS can take a while this is done by multiple processes that each process sends a limi...

updating column containing list separated by | using sql

I have a table named hr_Folders with following info:

Pivot/aggregation with percentage columns possible in t-sql?

Struggling with this a bit in SQL Server, any ideas? Given this data (ID is an INT, the rest are BITs):

Quicker way of finding duplicates in SQL Server

I'm trying to find a better way of finding duplicates in SQL Server. This took over 20 minutes to run with just over 300 million records before results started showing in the results window within...

How do I convert an int to a zero padded string in T-SQL?

Let's say I have an int with the value of 1. How can I convert that int to a zero padded string, such as 00000001?

How can I determine whether or not a stored procedure is recompiling every time?

I've got the a SQL Server stored procedure with the following T-SQL code contained within:

How do I select the number of distinct days in a date range?

I'm trying to use the T-SQL function DATEDIFF to select the number of distinct dates in a time period.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.