tsql

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? I have an application that reads pregenerated INSERT-statements and executes them against a database. To make the the data to be inserted culture independent I store datetime-values represented in the invariant culture (month/day/year...) . The ...

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 doesn't make sense. What is getting averaged? So I was about to start reverse engineering what Access does when it aggregates datetime using Av...

SQL date comparison

How do I check if the timestamp date of a record is before midnight today? datediff is driving me nuts... ...

How do I flush the PRINT buffer in TSQL?

I have a very long-running stored procedure in SQL Server 2005 that I'm trying to debug, and I'm using the 'print' command to do it. The problem is, I'm only getting the messages back from SQL Server at the very end of my sproc - I'd like to be able to flush the message buffer and see these messages immediately during the sproc's runtime...

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: Msg 156, Level 15, State 1, Procedure CalendarTable, Line 39 Incorrect syntax near the keyword 'OPTION'. is it because of my WITH statement as I can run the same code fine in a stored procedure? SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- =============...

What do I gain by adding a timestamp column called recordversion to a table in ms-sql?

What do I gain by adding a timestamp column called recordversion to a table in ms-sql? ...

How do I conditionally create a table in Sybase (TSQL)?

OK, so Sybase (12.5.4) will let me do the following to DROP a table if it already exists: IF EXISTS ( SELECT 1 FROM sysobjects WHERE name = 'a_table' AND type = 'U' ) DROP TABLE a_table GO But if I try to do the same with table creation, I always get warned that the table already exists, because it went ahead and tried...

How can I use an SQL Pivot for this?

I have a data set that is organized in the following manner: Timestamp|A0001|A0002|A0003|A0004|B0001|B0002|B0003|B0004 ... ---------+-----+-----+-----+-----+-----+-----+-----+----- 2008-1-1 | 1 | 2 | 10 | 6 | 20 | 35 | 300 | 8 2008-1-2 | 5 | 2 | 9 | 3 | 50 | 38 | 290 | 2 2008-1-4 | 7 | 7 | 11 | 0 | 30 | ...

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

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). I'm using theses inside larger queries as joined computations and I'm wondering if the query plan optimizer work well with them in every condition or if I'm bett...

How do I select last 5 rows in a table without sorting?

I want to select the last 5 records from a table in SQL Server without arranging the table in ascending or descending order. ...

Pivot using SQL Server 2000

I need some urgent help! I put together a sample scenario of my issue and I hope its enough for someone to point me in the right direction. I have two tables Products Product Meta I need a result set of the following ...

T-SQL: Opposite to string concatenation - how to split string into multiple records

I have seen a couple of questions related to string concatenation in SQL. I wonder how would you approach the opposite problem: splitting coma delimited string into rows of data: Lets say I have tables: userTypedTags(userID,commaSeparatedTags) 'one entry per user tags(tagID,name) And want to insert data into table userTag(userID,tag...

How to generate a Mandelbrot with T-SQL?

Learning a little about T-SQL, and thought an interesting exercise would be to generate a Mandelbrot set with it. Turns out someone already has (and recently, it appears). I'll let someone else post it as an answer, but I'm curious what optimizations can be made. Alternately, what would you do to make the code more readable? I'll sel...

Determining database from which function in separate utility database is being called

I have several databases, one of which contains utility functions called from the other databases. Is there a way in the utility functions to determine which database a function is being called from? ...

how can i write a sql query that finds rows where one column is a substring of another column

I wish to find all rows in a table where one column is a substring of another column. In other words, suppose I have a table (called people) with two columns: firstname and lastname, and I want to find all people like "rob robinowitz" and "jill bajillion". Is there a way to do something like "select * from people where lastname like %f...

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. The select is more or less like this: SELECT * FROM SomeTable WHERE (CONTAINS(Column1, 'word1 OR word2') AND CONTAINS(Column2, 'word3 OR word4')) now ...

How to performance test nested Sybase stored procedures?

I am looking for any tool which will allow the performance testing/tuning of Sybase nested stored procedures. There are many tools around and of course Sybase's own for performance tuning and testing SQL but none of these can handle nested stored procedures (i.e. a stored proc calling another stored proc). Does anyone have/know of such a...

SQL if statement in where clause for searching database

I'm creating a stored procedure to return search results where some of the parameters are optional. I want an "if statement" in my where clause but can't get it working. The where clause should filter by only the non-null parameters. Here's the sp ALTER PROCEDURE spVillaGet -- Add the parameters for the stored procedure here @accomod...

Is a dynamic sql stored procedure a bad thing for lots of records?

Hello, I have a table with almost 800,000 records and I am currently using dynamic sql to generate the query on the back end. The front end is a search page which takes about 20 parameters and depending on if a parameter was chosen, it adds an " AND ..." to the base query. I'm curious as to if dynamic sql is the right way to go ( does...