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...
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...
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 ...
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 ...
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...
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...
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...
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 ...
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...
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
...
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, ...
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...
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...
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...
Is there any equivalent to %TYPE in MSSQL2005?
CREATE TABLE TEST (ID NUMBER(5));
DECLARE
myVar TEST.ID%TYPE;
BEGIN
................
END;
...
I want to move a table into a specific Schema using T-SQL? I am using SQL Server 2008.
...
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...
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...
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 (...
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...