Hi friends, In the book Inside Microsoft SQL Server™ 2005 T-SQL Querying, the author Itzik Ben-Gan tell us the order or SQL Server 2005's logical query processing is: (8) SELECT (9) DISTINCT (11) <TOP_specification> <select_list> (1) FROM <left_table> (3) <join_type> JOIN <right_table> (2) ON <join_condition> (4) WHERE <...
Say if using sp_helplognis, want to view result set with filter UserName=db_owner. Is there any way besides output the original result set to a temp table and then query on that temp table? Thanks. ...
Hello everyone, I am using SQL Server 2008 Enterprise on Windows Server 2008 Enterprise. I have a question about tsql in SQL Server 2008. For select-where statement, there are two differnet forms, (1) select where foo between [some value] and [some other value], (2) select where foo >= [some value] and foo <= [some other value]? I am ...
The following query is a contrived example that demonstrates a bug I found in a stored procedure this week. CREATE TABLE #temp ( ID int IDENTITY(1,1), Value char(1) ) INSERT INTO #temp(Value) Values('a') INSERT INTO #temp(Value) Values('b') INSERT INTO #temp(Value) Values('c') INSERT INTO #temp(Value) Values('d') DECLARE @...
What's the best in performance to determined if an item exist or not specially if the table contain more than 700,000 row if (Select count(id) from Registeration where email='[email protected]') > 0 print 'Exist' else print 'Not Exist' OR if Exists(Select id from Registeration where email='[email protected]') print 'Exist' else ...
I am desiging a new table that will potentially have 200K rows. I would like to make sure that querys to this table are efficiant. in the past I had always given a row a unique id in the assumption that this would result in an index: CREATE TABLE [dbo].[Equipment]( [EquipID] [nchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, ...
Environment: SQL Server 2005 I have a stored proc which receives comma separated value in one parameter. I have written a Table Valued UDF function in SQL to break it and and return me it as a table. I am using that value to filter the data in the where clause of the stored proc. Everything works fine until there is NULL in that comma s...
Hi guys, In SQL Server 2005(2008 not tested), you can't create a temp function like #function_name, but you can create a functoin called function_name directly in tempdb. Does the function created in this way a temp function? What's the difference between a table called #table_name and the same named table directly created in tempdb? ...
I've just been reading up on NATURAL JOIN / USING - SQL92 features which are (sadly?) missing from SQL Server's current repertoire. Has anyone come from a DBMS that supported these to SQL Server (or another non-supporting DBMS) - were they as useful as they sound, or a can of worms (which also sounds possible!)? ...
Is it possible to do so using T-SQL alone? What query would you write against the system tables? ...
I have 4 tables namely Products, SalesLog, Breakages, SalesReturn. Products Table CREATE TABLE [dbo].[Products]( [ProductId] [int] IDENTITY(1,1) NOT NULL, [pName] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [pSize] [int] NULL, [pPrice] [decimal](10, 2) NULL, [pPackQty] [int] NULL, [pGroup] [int] NULL, [pCode] [int] NULL, ...
hello guys, I am having bad times to generate a rdlc report to show report in predefined format given by my client. Client given format As you can notice RED background is my problem area, Item "A" price has been changed thrice. And accordingly their sales, breakages and refund quantity is shown. I have 4 tables Products, SalesLog, Br...
I am getting the following error while executing the query . Please help Multipart identifier s.Company_id could not be bound INSERT INTO Company_Item_Company_List ( Company_id, Company_item_id, client_id, last_modified_timestamp, last_modified_user_id ) SELECT dcsl.distribution_center_id, sisl.Compan...
On bulk insert, Insert trigger only works for the first record and does not work for all the other records but trigger works properly when records are inserted using cursor. Insert trigger updates few columns of destination table. To insert bulk data I am using following script INSERT INTO DestinationTable (Column1, Column2) SELECT * F...
I need a query that will alter a single column from nvarchar(max) to 32. The real problem is this table has 800,000 rows. And my alter table myTable alter column mycolumn statement times out. Any suggestions or tips? ...
What is the best way to strip the date from a DATETIME so only time is left to do a comparison? I know I can do the following: CONVERT(DATETIME, CONVERT(VARCHAR(8), GETDATE(),8)) But this involves convert and characters. If I wanted to check whether a time (including minutes) was between two other times stored in DATETIME columns, ...
I cannot get the like statement to work with space and trailing wildcard. My query goes as follows: select * from Table where Field like 'Desc_%' The data is space-delimited such as, Desc Top, Desc Bottom and so on. The query works when I use the pattern 'Desc_%' but not when I use the pattern 'Desc %'. The field is nvarchar(255). A...
Is anyone aware of a T-SQL script that can detect redundant indexes across an entire database? An example of a redundant index in a table would be as follows: Index 1: 'ColumnA', 'ColumnB', 'ColumnC' Index 2: 'ColumnA', 'ColumnB' Ignoring other considerations, such as the width of columns and covering indexes, Index 2 would be redunda...
Hi, I have three tables in sql, CUSTOMER, ISSUE, ISSUE_NOTES. SELECT CUSTOMER.name, ISSUE.description, ISSUE_NOTES.notes FROM CUSTOMER, ISSUE, ISSUE_NOTES WHERE CUSTOMER.customer_id = ISSUE.customer_id AND ISSUE_NOTES.incident_id = ISSUE_NOTES.incident_id This will produce a row for each issue_notes field that's populated. (field is...
I have a spreadsheet with multiple columns, one of which is an owner_id column. The problem is that this column contains a comma delimited list of owner id's and not just a single one. I've imported this spreadsheet into my sql database (2008) and have completed other importing tasks and now have a parcel_id column as a result of this p...