tsql

When should I use Cross Apply over Inner Join?

What is the main purpose of using CROSS APPLY? I have read (vaguely, through posts on the Internet) that cross apply can be more efficient when selecting over large data sets if you are partitioning. (Paging comes to mind) I also know that CROSS APPLY doesn't require a UDF as the right-table. In most INNER JOIN queries (one-to-many re...

Constructing the FROM in SQL

I'm looking to pull a specific line from a number of table that have a field name criteria1. The problem I'm having is that when I combine the Owner and Table Name and try to call "select criteria1 from @t where linenum = 1" SQL is expecting @t to be a table. I need to know how to construct the full table name and then pass it to this qu...

SQL Server Query for Rank (RowNumber) and Groupings

I have a table that has some columns: User, Category, Value And I want to make a query that will give me a ranking, of all the users by the value, but reset for the category. Example: user1 CategoryA 10 user2 CategoryA 11 user3 CategoryA 9 user4 CategoryB 3 user1 CategoryB 11 the query would return: Rank User Category ...

How to check existence of a sql server object and drop it?

EDIT: the function creation was missing, sorry about that I have a T-SQL request that goes: DECLARE @IsSomething bit SET @IsSomething = 0 IF /some tests/ SET @IsSomething = 1 EXEC(' CREATE FUNCTION IsSomething () RETURNS bit AS BEGIN RETURN ' + @IsSomething + ' END') Of course if I run it twice I get There is already an object ...

Count total rows with a group by

I have the following query: select count(ords.TRACKING_NUM) from Orders ords (NoLock) group by ords.TRACKING_NUM having count(distinct ords.ORDER_NUM) = 4 I want it to retrieve the total amount of TRACKING_NUMs that have 4 ORDER_NUMs on them (should be 3,352). Instead I get 3,352 rows equal to 4 (or more because of the distinc...

Use SQL to export Parent/Child rows into a flat file

With an Orders table (OrderID, date, customerID, status, etc) and an OrderDetails table (ParentID, itemID, quantity, price, etc), I would like to create a SQL Query that will export a CSV flat file with Order and OrderDetail rows interspersed. For instance, output might look like this ("H" and "D" indicate "Header" and "Detail" respectiv...

Add a non null column to a sql table script - getting an error.

SQL Server 2005. The following 3 lines of sql work without error if the Is_Active column had previously existed in the Dim_Form table. ie if Is_Active has not existed previously, running the following 3 lines gives an error as displayed below; ALTER TABLE dbo.Dim_form add Is_Active bit NULL UPDATE dbo.Dim_form set Is_Active = 1 ALTER...

How do I refactor repetitive SQL update queries?

I have 3 tables. 1. Users 4 Cols UserID - User - RealName - Flags 2. UsersGroups 2 Cols UserID - GroupID 3. Groups 3 Cols GroupID - Group - Flags and I want to set the flags on the User table for User = 'Administrator' and apply the same to the Group table. I have the following SQL that works, but I also have several flags that I ...

SQL GROUP BY: Getting the most recently updated record for an indexed view

I'm trying to do row versioning using an indexed view, grouping records by their key and timestamp, and taking the max(timestamp) record. This is fine, but the query I have used (see the view below) does a self join, meaning it can't be used in an indexed view, which i think will be essential to performance. Is there a way to rewrite the...

SQL Server 2005 joining results of two different sp like ...

Hi, I want to join results of two different store procedures that returns same structure in following fashion: EXEC StoreProcedure1 p1 UNION EXEC StoreProcedure2 p2 I realize that is not possible, can sombody suggest elegant alternative? I beleive I should use temp table ? Thanks ...

How count the number of times a character appears in a SQL column?

For a user logging table I have in a SQL database, I track the some of the parameters off of a report request. The report allows multiple ID's to be passed to it and I store all of those in a single column in the database column. If this were to be a normalized set of data, there would definitely be an additional table setup for this, ...

Can a SQL Server stored proc determine its parent proc's name?

If Proc A executes Proc B, is there a way for Proc B to look-up that it was called by A instead of having a parameter where A passes B its ID? Per request: The reason I'm interested in this is multi-fold 1) General knowledge, I'm sure if it can be done it would involve clever use of some system tables/variables that may help me do othe...

Full text search failing on words with no stop lists

Hi, I am working on MsSQL 2008 server, I disabled all the stoplists that exist SELECT * FROM sys.fulltext_stopwords does not returna nything SELECT * FROM sys.fulltext_stoplists does not return anything either SELECT DISTINCT u.ID, Name FROM University u INNER JOIN CONTAINSTABLE(University, (Name), '"University" AND "of" AND "Phoenix...

Replace certain pattern in a long string in MS SQL using T-SQL

Hi all, I have a table in my MS SQL database where it has some incomplete data in a field. This field in question is a varchar field and has about 1000 characters in the field. This string consists of segmentations of words in the format of a forward slash followed by the segment and then ends with a forward slash (i.e. /p/). Each of...

T-SQL equivalent of PL/SQL %TYPE?

Is there any equivalent to %TYPE in MSSQL2005? CREATE TABLE TEST (ID NUMBER(5)); DECLARE myVar TEST.ID%TYPE; BEGIN ................ END; ...

How do I move a table into a schema in T-SQL

I want to move a table into a specific Schema using T-SQL? I am using SQL Server 2008. ...

Name of SQL Syntax notation?

Is there a name for a syntax used for SQL? E.g.) When you go to this page on BOL (SQL Books OnLine), it shows the syntax of BEGIN DIALOG CONVERSION BEGIN DIALOG [ CONVERSATION ] @dialog_handle FROM SERVICE initiator_service_name TO SERVICE 'target_service_name' [ , { 'service_broker_guid' | 'CURRENT DATABASE' } ] [ ON CON...

SQL Scripting Tool That Beats MS Query Analyzer?

I'm using MS Query Analyzer (as part of SQL Server 2000) to write T-SQL scripts to pull data out of a DB. This involves querying some tables, iterating through the results (using a cursor), some basic processing of the results and putting the processed data into another table. This is working pretty well as I can view the tables and stor...

INT vs Unique-Identifier for ID field in database

I am creating a new database for a web site using SQL Server 2005 (possibly SQL Server 2008 in the near future). As an application developer, I've seen many databases that use an integer (or bigint, etc.) for an ID field of a table that will be used for relationships. But lately I've also seen databases that use the unique identifier (...

MS-SQL problem - Only one expression can be specified in the select list

I write the following query: select id, (select NameEn from [Campaign] where id=CampaignId) as CampaignName, createdDate, (select Name, IdNo, Email, MobileNo from [Members] where id=MemberId) from Transactions and error occurs: "Only one expression can be specified in the select list when the subquery is not in...