Database peformance with joins

Possible Duplicate: When and why are database joins expensive? My company has assigned me a job of improving DB performance. Earlier developer has written a query having 5 innerjoins. What is the number of inner joins which can be used without hurting performance ?

Subquery with multiple results, SQL Server

I have a query that looks like this: SELECT * FROM dbo.document_library_file_attributes WHERE my_file_id= (SELECT my_file_id FROM dbo.document_library_file_attributes WHERE attribute_name=’Directory/Key’ AND attribute_value LIKE @directory+’%’) I want to the subquery to be able to return multiple results, meaning my_file_id could equal ‘directoryA’, ‘directoryB’, and I want the results for all of these. How would I do […]

SQL Using a var as a table name

I have this piece of code and instead of repeating the table name in each field every time I run the query, I just want to set the table name to a variable and use that, the code I have doesn’t work, can anyone point me in the right direction? DECLARE @table_name varchar(20) SET @table_name […]

How can I get a MD5 of an entire database and use it to poll for changes?

I’m trying to do some sort of polling to listen to updates occuring on a database. So far I was considering getting a MD5 hash (or any type or hash) of the database (or couple tables), store that in a variable, and run a thread that loops this procedure over and over, comparing it with […]

Indexing in SQLServer

Hi I am using SQLServer2008. I want to know what is index in SQLServer and how can i use it? This is part of my can i give index? Many Thanks.. DECLARE @TableMember TABLE ( BrokerId INT , RankId INT , MemberId INT , InstallmentId INT , PlanId INT , IntroducerId INT , Date […]

INSERT INTO table which requires a Join

Im currently trying to move data from one table to another. In a previous question I asked today I was advised to use an insert statement. While trying to run the statement i get an error from SQL Server as follows: Cannot insert the value NULL into column ‘Parent’, table ‘*****.dbo.Product’; column does not allow […]

t-sql: update in specific order determined by 'in' expression

Suppose: declare @counter int set @counter = 0 update MyTable set @counter = MyField = @counter + 1 where Some_ID in (select ID from SomeTable) What I want is that update happened in same order as select ID from SomeTable IDs are returned. Is there a nice way to do this?

Rows into Columns In SQL Server (May be Pivot)

I need to transform Rows data into columns on a specif condition. Assume that we are in year of 2015 then It needs to fetch next years data as a new column. Please have a look at the Table A and can some one suggest how can I achieve expected result? Table A: EmpID Year […]

Why does query execute on its own but not in a stored procedure

I am in the process of writing some stored procedures to transfer data from tables used for the current years records to tables used to store historical data. One example I am working on at present is a typical Master- Detail relationship, where the detail records obviously need to be processed before the master records. […]

How to filter the columns?

Using SQL Server 2005 I want to filter the column where column value not equal to numeric Table1 Status 010203 Absent 231415 Ramesh Suresh …, I want to get only Names from the status column like Tried Query Select Status from table1 where status <> ‘absent’ and status <> numecivalue How to mentioned status <> […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.