When I am writing queries, I will do so in steps. Sometimes, in the process I will realize that I've made a "mistake" such as ending up with extra or losing records. So, I will typically compare the two queries like so: (Select blah blah blah ) Mine Inner join ((Select blah blah blah ) Orig Where Mine.PK <> Orig.PK or if I'm lookin...
Creating Comma Separated Lists In SQL Hi All, I am attempting to merge something like this in my SQL Server database: [TicketID], [Person] T0001 Alice T0001 Bob T0002 Catherine T0002 Doug T0003 Elaine Into this: [TicketID], [People] T0001 Alice, Bob T0002 Catherine, Doug T0003 ...
I have the following table: if object_id(N'dbo.Node') is null create table dbo.Node ( ID bigint identity primary key, ParentID bigint null foreign key references Node(ID) on delete no action, DateCreated datetime not null, LastUpdated datetime not null, [Name] nvarchar(500) not null, ); Now, because SQL Server comp...
DECLARE @t TABLE(Words VARCHAR(100)) INSERT INTO @t SELECT 'Stack Overflow' UNION ALL SELECT 'EQUATORIAL' SELECT * FROM @t WHERE Words LIKE '%[AEIOU]%' I am getting both as the output Words Stack Overflow EQUATORIAL The desired output being EQUATORIAL Thanks ...
Hi guys, Is there any way to send an SMS through SQL Server 2008? ...
I've got a T-SQL script, that converts field to IDENTITY (in a weird way). How do I convert it to PL/SQL? (and, probably, figure out, if there is a simpler way to do this - without creating a temporary table). The T-SQL script: -- alter table ts_changes add TS_THREADID VARCHAR(100) NULL; -- Change Field TS_ID TS_NOTIFICATIONEVENTS t...
I have the following query: SELECT DISTINCT(po.SONumber) AS [Sales Order No_], po.PONumber AS PoNo, ph.[Buy-from Vendor No_] AS VendorNo, ph.[Pay-to Name], ph.[Document Date], 'Ship-to Name' = CASE WHEN sh.[Ship-to Name] > '' THEN sh.[Ship-to Name] ELSE sih.[Ship-to Name] END, 'Ship-to Post Code' = CASE WHEN sh.[Ship-to Post Cod...
I have the following table: if object_id(N'dbo.Node') is null create table dbo.Node ( ID bigint identity primary key, ParentID bigint null, -- references Node(ID) DateCreated datetime not null, LastUpdated datetime not null, [Name] nvarchar(500) not null, ); I have this trigger to achieve cascading deletes within t...
Actually I am a newbie in SQL SERVER (only 2 months as of now) and want to learn more and master it over the coming days. So I have asked this question. I believe it will help people like me a lot to grow if we follow those that the experienced peoples here have faced or seen so far in their career. Also it will help us in our job or ...
Hello all, How can I read the first row of a text file (comma separated) and use that as the field names of a new table I want to create in SQL Server? The field names can all be varchar. Thanks all UPDTAE Please note I would like to do this using T-SQL or any other way (sqlcmd etc). I do not want to use the wizard. ...
When we script all Sql Server Database objects from Management Studio by right clicking on the database, and go to tasks and select "generate scripts", will the generated scripts be in order??? ...
The password field in my user table (SQL Server 2008) is encrypted using HASHBYTES on insertion. I have a stored procedure with parameters for the username and plain-text password which does a SELECT using that username and the password sent through HASHBYTES, then returns the user record if it finds a match. The SP is always returning...
I'm using SQL Server 2005 for the first time, having mostly worked with MySQL in the past. I'm used to using auto_increment to create unique IDs in tables. Anyway... I'm working in a java app, and need to do the following. Presume my table has two columns: itemID (int) and itemValue(int). This is basically what I want to do (the dbco...
I have a deceptively simple SQL Server query that's taking a lot longer than I would expect. SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT COUNT(DISTINCT(guid)) FROM listens WHERE url='http://www.sample.com/' 'guid' is varchar(64) NULL 'url' is varchar(900) NULL There is an index on guid and url. There are over 7 million ...
Relevant tables: DepartmentPhone: DepartmentPhoneID int, DepartmentID int, PhoneID int Phone: PhoneID int, PhoneType int There are 6 phones with PhoneType=4 that belong to DepartmentID=2. So this produces 6 records: select * from DepartmentPhone join Phone on Phone.PhoneID = DepartmentPhone.PhoneID and Phone.PhoneType = 4 where Depar...
i have a column in my sql server 2005 table that should hold the number of months an employee has been in service. Since i also have the date the employee was engaged, i want the "months_In_Service" column to be a computed column. now if i use datediff(month,[DateEngaged],getdate()) as the formula for the months in service computed colum...
Hi all, I ve got the following query which is throwing the following error Unkown Column 'RowNum' WITH Employees AS ( SELECT (keyTblSp.RANK * 3) AS [Rank], sp.*, addr.Street, addr.PostOfficeBox, addr.StreetNumber FROM Employee sp INNER JOIN FREETEXTTABLE(Employee, *, 'something', 1000) AS keyTbl...
Hi all, I'm trying to create a column that contains all cities of the referenced addresses. DECLARE @AddressList nvarchar(max) SELECT @AddressList = COALESCE(@AddressList + ' ', '') + City FROM [Address] SELECT Employee.*, (SELECT @AddressList) AS AddressCities FROM Employee But I dont know where to put the WHERE clause. ...
If I'm am trying to squeeze every last drop of performance out of a query what affect does having these types of index's being used by my joins. clustered index. non-clustered index. clustered or non-clustered index with extra columns that may not be involved in the join. Will I gain any performance if I go through and create cluster...
Hi, I'm looking for a good solution to use the containstable feature of the SQL Serve r2005 effectivly. Currently I have, e.g. an Employee and an Address table. -Employee Id Name -Address Id Street City EmployeeId Now the user can enter search terms in only one textbox and I want this terms to be split and search with an "AND" opera...