tsql

T-sql range Query

I want to do something similar to below. I have the pseudo-logic below. Just wonder how to implement this: delete B from TableA A with (nolock), TableB B with (nolock) where A.BeginNumber <= B.startipnum and A.Endnumber >= B.endipnum delete A from TableA A with (nolock), TableB B with (nolock) where A.BeginNumber => B.startipnum and ...

Displaying Multi-Value Parameters.

Is this really the best way to handle this? I have a multivalued parameter which has about 25 elements in it. If the user selects "Select All" and I use the standard (at least as far as I know) method of displaying the parameter at the top of the report: =join(Parameters!ProductClass.Value, ",") Rather than the the word "All" or some...

Best practice for operating on large amounts of data

I need to do a lot of processing on a table that has 26+ million rows: Determine correct size of each column based on said column's data Identify and remove duplicate rows. Create a primary key (auto incrementing id) Create a natural key (unique constraint) Add and remove columns Please list your tips on how to speed this process up ...

Select Top (some number) with UNION ALL and ORDER BY

I am trying to pull back the top five instances of certain records loaded into a table. It is important to note that I am trying to get my results out of the same table and I think there is a problem that I can't quite figure out related to the fact that this is one table. Here is the example of my query: Select * From ( Select Top 5 ...

SQL optimization/tweaking

I have this SQL Server query SELECT count(distinct [IP]) as GlobalUniqueIPcount, --RangeUniqueIPcount (SELECT count(distinct [IP]) FROM [tblSequence] WHERE SiteID = @siteID AND ([Timestamp] > DATEADD(dd, -@days, (LEFT(GETDATE(),12))))) as RangeUniqueIPcount, --RangeUrlUniqueIPcount (SELECT count(distinct [IP]) FROM [tbl...

T-SQL: exporting table to XML using stored proc - how to format?

I'm using SELECT ... FOR XML to generate XML and it creates exactly what I want to see - in SSMS. But... The problem I have is, the exported XML file has the entire XML body on a single line. It's valid XML, but how can I format the output? (one element per line, terminated with \r\n and preferably indented) The file should be human ...

SQL Server SSMS: how to get row numbers for a batch

In SSMS, how can I get SSMS to show row numbers for a batch? I can get row numbers for a query window, but that does not help when an error occurs in batch statement. This feature would help me a lot to find errors from my code. ...

T-SQL query where column only contains numbers.

Possible Duplicate: How to get only numeric column values? I am trying to write a T-SQL query that only returns rows where a specific column only contains numbers. something like select * from table where column like '[0-9]%' The problem is the column can be 1 - 10 characters long. ...

SQL ROW_NUMBER() over performance problem

I have this SQL that works fine. Want the my filter to return the LATEST unique SessionGuids with the highest UserSessionSequenceID. Problem is performance sucks - even though I have good indexes. How can I rewrite this - to omit the ROW_NUMBER line? SELECT TOP(@resultCount) * FROM ( SELECT [UserSessionSequenceID] ...

help with SQL query?

Hi Guys, I am not sure if I should do this in the code or do it in the query, but I will ask it here as I am interested in a sql solution for myself. Say if I have the following table, and I need to get all the rows whose ParentSID is 1. But if any of these rows return have Null as FID then I also need to go fetch all rows whose Parent...

How to change the schema for a diagram?

Hi, Diagrams created with Microsoft SQL Server Management Studio have their default schema set to dbo. It seems that there is no way to set another schema when creating a diagram. For tables, alter schema NewSchemaName transfer dbo.TableName moves a specified table to the new schema. But the same command does not work for diagrams. ...

how to select comments for each row

I have events table which include so many cols besides Id PK and table Comments which includes text, eventId and Id PK . how can I select the event information and it's comments in one single sql statement, how to use it and how it should look like !? Note that EventId(Comments) = Id(events) ...

how to write query for optional parameters

hello friends i am following 3 layer architecture for my project i mean presentation layer, business logic layer and finally database layer my question is that i am making a search feature for my project where i need to send parameter through a method for search query which is to make on business logic layer i am to send parameter from ...

T-SQL optimizing performance of various stored procedures question.

Hello, so I have written several stored procedures that act on individual rows of data by taking in an ID number. I would like to keep several stored procedures that can call this stored procedure at different levels of my database scheme. For instance, when a row is inserted I call this stored procedure. When something else is modified ...

How to use Fortify to analyse T-SQL

sourceanalyzer -b ID ttt.sql (no error) sourceanalyzer -b ID -scan -f result.fpr (no error) But when I used Fortify Audit Workbench to open the result.fpr file,there is nothing in Issues(no Hot,no Warning,no Info). Was I missed out any parameters in the sourceanalyzer's command ? ...

How do I improve full-text searching

Hello, I have a SQL Server 2008 database. This database has 2 tables: Manufacturer ------------ ID, Name nvarchar(256) Product ------- ID ManufacturerID Name nvarchar(256) My application has a search box. I do not know if the user is going to provide a manufacturer name or a product name. In addition, I'm trying to be a little bit g...

date ranges in where clause of index view

I have an index view which I run a query against with WHERE GETDATE() BETWEEN start_date AND end_date in the WHERE clause. I suspect that the answer to my question is probably going to be no, but, is it possible to push this into the index view or do something clever to achieve the same effect? My concern is that what the index vi...

Dynamic dependency view SQL and SSIS packages

Hi All, In my enterprise project we have nearly 800 tables with 7 staging areas. We have developed nearly 100 packages to implement the DW. Business users constantly chaging the requirements after completing all the packages like table/column names, PK/FK columns with many other changes due to business requirements. Being changes in t...

Updating every row in a table with IF Statement conditions

Hi everyone, this one has me stuck fast. Data: Month Total Impact Forecast ------------------------------------------------ 2010-04-01 45792.0000 1.0000 NULL 2010-05-01 51789.0000 1.0000 NULL 2010-06-01 58228.0000 1.0000 NULL 2010-07-01 52956.5217 1.0000 NULL 2010-08-01 53600....

sql query to avoid null value parameter

these are the variables i am passing to some method where i need to write some sql query like string cmd = @"select * from students_tbl where course_id=@courseId and branch_id=in(+" branchId "+) and passout_year>=@passoutYear and current_backlog>=@currentBacklog and gender=@gender and eGap<=@eGap and first_year_percent>=@firstyearP...