If I have an UDF that returns a table, with thousands of rows, but I just want a particular row from that rowset, will SQL Server be able to handle this effciently? DECLARE @pID int; --... SELECT * FROM dbo.MyTableUDF(@pID) WHERE SomeColumn BETWEEN 1 AND 2 OR SomeOtherColumn LIKE 'this_pattern&' To what extent is the query optimizer c...
Hi, How can we check that table have index or not ? if have how to find that index for a particular column for a table? Regards, kumar ...
Hello, I got a strange problem. Let me give you some information. There is a stored procedure having one select returning the set of data and the return value of 0. Before the "main" select in the sp, there's also fake select (because of SSIS and codegen tools) looking like this : IF (1=0) BEGIN CONVERT(INT, NULL) AS Id ,CONVERT...
I want to move data from one database's table to another database's table. It is giving me a foreign key error. Please tell me how I can insert all those data which is valid except those rows who don't have a foreign key. I am using SQL Server 2005 My query is : SET IDENTITY_INSERT City ON INSERT INTO City ([cityid],[city],[countr...
Hi, I wanna to have query: Select cars.* from cars where cars.code in ( select carCode from articles where numberofrecords with this car (it is not a column) >1 and Accepted=1 order by date ) How to write it? ...
Hi In bulk insertion only in the last record trigger is executing. If I bulk insert 10 records the trigger is running only for the 10th record. But I need trigger to run on all 10 records. Please help me with an example ...
Hi all, I have a table (ROOMUSAGE) containing the times people check in and out of rooms grouped by PERSONKEY and ROOMKEY. It looks like this: PERSONKEY | ROOMKEY | CHECKIN | CHECKOUT | ROW ---------------------------------------------------------------- 1 | 8 | 13-4-2010 10:00 | 13-4-2010 11:00 | 1 1...
What is the easiest way to automatically fill a datetime column in an SQL data table with the date the row was created? Using SQL Server 2005 if that matters. EDIT: I've tried using GETDATE() for default and that works for an INSERT Query but not when the TableAdapter adds the row. ...
Hi, I've got table Articles ID identity autoincement, IDArticle: nvarchar(100) ,IDCar nvarchar(100), createddate How to convert this: SELECT IDCar , MAX(createddate) FROM Articles GROUP BY IDCar to get IDArticle eg: 1 art1 BWM 5-21-2010 2 art2 BMW 5-24-2010 3 art3 BMW 5-31-2010 4 art4 Porshe 5-31-2010 5 art5 Porshe 6-1-2010 Ex...
I have 3 computers having the same sql server 2005 database, i would like to gather the data from the 3 computers to another computer which has the same database. pls help me tnx in advance ...
How can I get an output as follows using FOR XML / sql query. I am not sure how I can get the Column Values as Elements instead of the tables' column Names. I am using sql server 2005 I HAVE TABLE SCEMA AS FOLLOWS CREATE TABLE PARENT ( PID INT, PNAME VARCHAR(20) ) CREATE TABLE CHILD ( PID INT, CID INT, CNAME VARCHAR(20) ) CREATE...
i tried something like this but the id value is not changing, getting the value for first record and set the value for all rest... insert into table1 (Id,b,c,d) (select (select max(Id)+1 from table1),x,y,z from table2 where... ) ...
Hi, Is it possible to do a full text search in database . Say if i want to search for the text "Robert" from any table and any column in a database. How can we do this. is there any option to do it ? Regards, Radha A ...
declare @t1 Table ( a1 int ) insert into @t1 select top 10 AnimalID from Animal --select * from @t1 declare @t2 table ( dogs int null ) update @t2 set dogs = (Select COUNT(*) from @t1) ---------> The out put it gives me is just 0 ...
Based on an existing table I used CTE recursive query to come up with following data. But failing to apply it a level further. Data is as below id name parentid -------------------------- 1 project 0 2 structure 1 3 path_1 2 4 path_2 2 5 path_3 2 6 path_4 3 7 path_5 4 8 path_6 ...
Sorry for the lame title, my descriptive skills are poor today. In a nutshell, I have a query similar to the following: SELECT P.LAST_NAME, P.FIRST_NAME, D.DEMO_GROUP FROM PERSON P JOIN PERSON_DEMOGRAPHIC PD ON PD.PERSON_ID = P.PERSON_ID JOIN DEMOGRAPHIC D ON D.DEMOGRAPHIC_ID = PD.DEMOGRAPHIC_ID This returns output like this: LAST_N...
Are there any constants in T-SQL like there are in some other languages that provide the max and min values ranges of data types such as int? I have a code table where each row has an upper and lower range column, and I need an entry that represents a range where the upper range is the maximum value an int can hold(sort of like a hackis...
I am making an URL shortener, and I am struggling with the optimal way of encoding a number (id) into a character string. I am using the characters 0-9,A-Z,a-z so it will basically be a base-62 encoding. That is pretty basic, but it doesn't make use of all possible codes. The codes that it would produce would be: 0, 1, ... y, z, 10, 11...
Trying to put a single query together to be used eventually in a SQL Server 2005 report. I need to: Pull in all distinct records for values in the "eventid" column for a time frame - this seems to work. For each eventid referenced above, I need to search for all instances of the same eventid to see if there is another record with TaskN...
I have many .sql files with lots of empty lines e.g. WITH cteTotalSales (SalesPersonID, NetSales) AS ( SELECT SalesPersonID, ROUND(SUM(SubTotal), 2) FROM Sales.SalesOrderHeader WHERE SalesPersonID IS NOT NULL GROUP BY SalesPersonID ) SELECT sp.FirstName + ' ' + sp.LastName AS FullName, sp.City + ',...