Hi , I know the DENSE_RANK() function of the ranking function and its job well. But always I only write this function’s name and it do its job implicitly can cannot see how it work explicitly and I like to see its structure which do the operations within this function with T_SQL language. Would you please help me with it. ...
Imagine a table with like a hundred of different columns in it. Imagine, then, that I have a user-data table from where I want to copy data to the base table. So I wrote this simple insert-select statement and this error pops up. So, what's the most elegant way to figure out which column raises the error? My initial thoughts on the solu...
i have a column with dates, but it is a varchar: 8/31/2010 9:48 8/31/2010 9:49 8/31/2010 9:51 8/31/2010 9:52 8/31/2010 9:55 8/31/2010 9:59 8/31/2010 10:11 8/31/2010 10:13 8/31/2010 10:16 8/31/2010 10:37 8/31/2010 10:42 i made sure that none of these will be a BAD date: SELECT * FROM qcvalues.dbo.batchinfo WHERE ISDATE(reporttime) <> ...
I have data in following format Table1 e_id e_name e_type ------------------------- 1 CBC 2 2 ABC 3 3 N2 1 4 CBC1 3 5 ABC1 3 6 N1 1 table2 N_ID N_Name --------------- 3 N2 6 N1 Table3 N_ID E_ID ------------ 3 1 3 2 3 3 6 4 6 5 6 6 And...
I wanted to do an insert into with a union, and someone suggested this: SELECT x INTO ##temp FROM (SELECT x FROM y UNION ALL SELECT x FROM z) UN It works, but what is the UN? Unfortunately, Googling for "t-sql un" isn't very helpful :p Note: I found out that you can just do SELECT x INTO ##temp FROM y UNION ALL SELECT x FROM b but I'...
Hello everyone, I am using SQL Server 2008 Enterprise. I am using the following statement in SQL Server Management Studio as a part of a store procedure, and there is following error (compile error when I press F5 to run the store procedure). But when I removed count(), all error disappears. Any ideas what is wrong with count()? Another...
In a query, I have a column like this: case when X = 1 then case when Y <> 0 then YY else XX end else ZZ end as MyColumn Is there a way, in another column, to check the above column value referencing MyColumn instead of rewriting the case statement? I should value another column based on the MyColumn value. Thanks ...
I have a problem with SQL Server 2008 full text search I have the following query SELECT * FROM cigars WHERE CONTAINS(cigars.*, @Search ) @Search is a value that is passed in through a stored procedure. But, thats not the problem. What is is if someone searches for say 'Punch' it works fine and I get all cigars that match. Where the...
Hello everyone, I am using SQL Server 2008 Enterprise. Now I have two patterns to implement the same function (to see if zoo1 or zoo2 or zoo3 column contains some text, in pattern 1, I merge content of zoo1, zoo2 and zoo3 to form a new column called zoo), I think the first pattern is always of better performance (I have made some experi...
Hi All, Trying to understand why you can't use sql_variant for the value type when using the XML nodes function within SQL Server? I have a scenario where I will be dynamically parsing some XML input and being able to use the sql_variant would be a nice alternative to having to assign a datatype variable or doing a lookup on sys.columns...
Ok I have a database that looks as follows: Orders, Services, OrderUnits, Notes Orders: id Services: id, orderid Units: id, Orderid, unitnumber Notes: id, relatedid, text, date Orders have 1 to many order units Orders have 1 to many services Services have 1 to many notes I am trying to relate only the notes that relate to the unit ...
Hello, I need some help analysing some simple t-sql execution plans. I have a table [User] with the following 3 columns: Id(Primary Key), Username(nvarchar(50)), FirstName(nvarchar(50)) I have Created an Unique NonClustered Index for the Username column The execution plan for a query filtering by FirstName: select * from [User] where...
I have to create an association between 2 products, which has unique product_ids and insert them into an already constructed table. The association is created based on unique part number these product ids have. For instance: Product_id = 7578711 Part Number = 0101-2478 Product Id = 7957948 Part Number = 0101-2478 Product Id = 1055814...
I'm hoping there's a simple way to do this without using a sub-query: Scenario: You have "TableA" with columns "Key", "SubKey", and "Value". I need to get the "Value" of the MAX("SubKey") for a given "Key". So if the Table contained the rows: KEY SUBKEY VALUE 1 1 100 1 2 200 1 3 300 For Key = 1, I need the val...
I have an Indexed View with two columns, a primary key char and a field for full-text indexing varchar(300). I need to search from a MS Great Plains database, so I created the view to populate a field with concatenated values from my primary table IV00101. CREATE VIEW SearchablePartContent WITH SCHEMABINDING AS SELECT ITEMNMBR, rtrim(I...
If you drop a table in SQL Server that has Keys, Indexes, Contraints, etc on it will it drop those as well? I was just wondering when I am building my scripts if I need to create drop scripts for all of those as well or if I can simply drop the tables? Thanks, S ...
Hello, I am trying to debug a TSQL via Visual Studio 2005. I added the database connection and are now able to connect. But I initially I couldn't see the "Attach To Process" under tools. I went into the customize option and drag and dropped the "Attach To Process" under tools. Now I can see the "Attach To Process" under tools, but it i...
Hi, I have a table with 2 columns: ID, ID_PROJ_CSR the content of that table is: ID ID_PROJ_CSR 747 222 785 102 786 222 787 223 788 224 I want to select the ID, but if any value from ID_PROJ_CSR is a duplicate, I need to select any ID of the rows that contains that duplicate value (in that example, select ID 747 OR 786 ...
I'm using the UPDLOCK and READPAST sql hints in a stored procedure in order to implement a sort of a table queue (I say a sort because I select top 1500 instead of a top 1, and I don't delete the rows after I select them. For more details, see question http://stackoverflow.com/questions/3636950/return-unlocked-rows-in-a-select-top-n-quer...
I have 2 regular Tables "A" and "Q" and one junction table AQ dt looks like this; A * AID * Adescription * AScore * Username Q * QID * Qdescription * Qpoint AQ * AID * QID * Username I am looking for a tsql query that will help me update "AQ" to enable me output user performance base on their Answers ("A"Table) to Specific Ques...