how to split a string in TSQL
I have a varchar @a='a|b|c|d|e|f|g|h|i|j|k|l|m|n|o|p' , wich have | delimitted values. I want to split this variable in a array or a table. Do anyone have any idea about this. ...
tsql
Query to update rowNum
Can anyone help me write this query more efficiently? I have a table that captures TCP traffic, and I'd like to update a column called RowNumForFlow which is simly the sequential number of the IP packet in that flow. The code below works fine, but it is slow. declare @FlowID int declare @LastRowNumInFlow int declare @counter1 int set ...
Making rows in to colums using sql query
I have a temp table in which have one coloum with four rows. Table ------ vaibhav IBM 12 'T' I need a temp table Col1 Col2 Col3 Col4 ------ ----- ----- ------ Vaibhav IBM 12 'T' Do anyone have any idea. ...
Sql Server query performance
I have a stored procedure on a busy database which constantly come out top in the list of expensive queries (by some way). The query is very simple, it takes a single parameter (@ID, int) which is the primary key of the table, and selects the record that matches that ID. The primary key is an identity field with a clustered index, so I...
Optimal search queries
Following on from my last question http://stackoverflow.com/questions/2788082/sql-server-query-performance, and discovering that my method of allowing optional parameters in a search query is sub optimal, does anyone have guidelines on how to approach this? For example, say I have an application table, a customer table and a contact det...
tsql script to add delete cascade to existing tables
Hi is there a script that a can use to enable cascading when deleting a record from an exsiting table Thankd ...
How do I make a function in SQL Server that accepts a column of data?
I made the following function in SQL Server 2008 earlier this week that takes two parameters and uses them to select a column of "detail" records and returns them as a single varchar list of comma separated values. Now that I get to thinking about it, I would like to take this table and application-specific function and make it more gen...
Left Join Returning Extra Rows T-SQL?
I have the following query: select * from ACADEMIC a left join RESIDENCY r on a.PEOPLE_CODE_ID = r.PEOPLE_CODE_ID where a.ACADEMIC_TERM='Fall' and r.ACADEMIC_TERM='Fall' and a.ACADEMIC_SESSION='' and a.ACADEMIC_YEAR = (Select Year(GetDate())) and r.ACADEMIC_YEAR = (Select Year(GetDate())) and (CLASS_LEVEL LIKE 'FR%' OR a.CLAS...
How do I get a scalar value from a stored proc using Nettiers
I have a really simple stored procedure that looks like this: CREATE PROCEDURE _Visitor_GetVisitorIDByVisitorGUID ( @VisitorGUID AS UNIQUEIDENTIFIER ) AS DECLARE @VisitorID AS bigint SELECT @VisitorID = VisitorID FROM dbo.Visitor WHERE VisitorGUID = @VisitorGUID --Here's what I've tried RETURN @VisitorID 'Returns an IDataReader SE...
Converting data type varchar to numeric
Msg 8114, Level 16, State 5, Procedure spGetDetails, Line 88 Error converting data type varchar to numeric. I have already converted this @mfr_id to int type then also getting the above error. I'm getting error while executing stored procedure. The line which I'm getting error is: if(@mfr_id = 5) ...
SQL 2008 CASE statement aggravation...
Why does this fail: DECLARE @DATE VARCHAR(50) = 'dasf' SELECT CASE WHEN ISDATE(@DATE) = 1 THEN CONVERT(date,@DATE) ELSE @DATE END Msg 241, Level 16, State 1, Line 2 Conversion failed when converting date and/or time from character string. Why is it trying to convert dasf to date when it clearly causes ISDATE(@DATE) = 1 to eva...
SQL Server 2008 > how to get service startup parameter values by query?
Is it possible to query for the SQL Server 2008 service startup parameter values using T-SQL? I'm specifically looking for the -g parameter that indicates how much memory that SQL Server will leave available for memory allocations within the SQL Server process, but outside the SQL Server memory pool [msdn reference]. ...
Microsoft T-SQL Counting Consecutive Records
Problem: From the most current day per person, count the number of consecutive days that each person has received 0 points for being good. Sample data to work from : Date Name Points 2010-05-07 Jane 0 2010-05-06 Jane 1 2010-05-07 John 0 2010-05-06 John 0 2010-05-05 John 0 2010-05-04 John 0 2010-05-03 John 1 2010-05...
SQL Server Index Scripting
I'm using SQL Server 2008. Is it possible to create a script to loop over all tables in a database generating a set of index drop scripts and create scripts separately? What I have to do is drop all indexes on a set of databases to run a heavy data load process but then I want to re-enable all the indexes. I don't want to have to go th...
TSQL - TOP X in FROM Subquery?
Can someone please enlighten me to a way to filter a subquery that is located in a FROM clause? I would like it to look something like this: SELECT * FROM TABLE_A LEFT JOIN (TOP 8 TABLE_B) ON TABLE_B.id = TABLE_A.id ...
query to get part of a string
column1 \\abc\tri\eds\rf1\edr\4ed \\f.d\tri\ef\poe \\ghi0j\tri\gf\rf\k\hg\ose ' ' ' i got some rows like that in a column now i want to get the result set like \\abc\tri\eds \\f.d\tri\ef\ \\ghij\tri\gf simply from first '\' to end of 4th '\' ...
Dynamic query runs directly but not through variable, what could be the reason?
Here is my scenario, I'm creating a dynamic query using a select statement which uses functions to generate the query. I am storing it into a variable and running it using exec. i.e. declare @dsql nvarchar(max) set @dsql = '' select @dsql = @dsql + dbo.getDynmicQuery(column1, column2) from Table1 exec(@dsql) Now it produces the many...
Rename the table Column
Hi all, I am trying to execute the following query. I don't have 'CrewID' column so in that case it will by pass update part of the script. but it gives error Invalid object CrewID'. Can you please tell me why it excute update part even my if condition does not matched. Is there is another way to do the same. I have the requirement where...
Decimal data Type Display scale part as zero
I have Decimal field in SQLserver 2005 table, Price decimal(18, 4) if I write 12 it will be converted to 12.0000, if I write 12.33 it will be converted into 12.3300. Always it's putting zero to the right of the decimal point in the count of Scale Part(4). I was using these in SQL Server 2000, it was not behaving like this, i...
How do I specify a recovery model (full, simple or bulk-logged) when creating a database in T-SQL code?
How do I specify a recovery model (full, simple or bulk-logged) when creating a database in a CREATE DATABASE query in T-SQL code? ...