Which one have better performance : Derived Tables or Temporary Tables
Sometimes we can write a query with both derived table and temporary table. my question is that which one is better? why? ...
tsql
Check if only single record exists.
I have a table with GID ID DefaultA DefaultB DefaultC DefaultD DefaultE DefaultF my requirement is that there should always be one record in the database always. In the front end if the end user tries to update its ok since he will update the existing record.But if he tries to enter a new record I must stop him. ...
Query SQL Server 2005 Database Table From Excel Spreadsheet
Scenario I have a database made up of 4 columns and 6 million rows. I want to be able to be able to use Excel to query this data in the same way in which I can query it using the Sql Server Management Studio. I've had a google around and read a few articles but in all honesty I dont really know enough about the implementation to really...
Table design options for large number of rows?
I have an application that sends data based on user interaction (not user input). The data sent could be an Integer, String, Date, or Boolean value. There are 140 keys. We may get anywhere from 1 key value pair to all 140 at a time. We want to store everything but will only be using 20 out of 140 keys within the application. The remaini...
SQL Server 2005: Select that Joins Tables from a Remote Server Runs Too Slowly - When I "select min(column)"
Update: After some experimentation, it appears that the thing that is slow is when I call "SELECT min(column) FROM theTableValuedFunction()". I'll call the local server ServerLocal and the other remote server ServerForeign. I have a SQL query in a table valued function on ServerLocal that looks like this: SELECT columns FROM ServerFo...
SQL delete unique row in table
I've got the following table: PatientID | DiagID ...where PatientID is a unique identifier and DiagID is a diagnosis ID, which is not unique. How do I delete the patient from table who's got unique DiagID (the only in the table, who's got this specific DiagID)? ...
custom control for TFS work item type
I will explain my question with an example. Lets consider TFS "Bug" work item type. In the bug work item type there is a "Developer" and "Tester" dropdown. Using the inbuilt rule types we can only assign "VALIDUSER" to each drop down. This displays all the windows user in the drop down. I want to be able to create a custom subteam under ...
SQL select row into a string variable without knowing columns
Hello, I am new to writing SQL and would greatly appreciate help on this problem. :) I am trying to select an entire row into a string, preferably separated by a space or a comma. I would like to accomplish this in a generic way, without having to know specifics about the columns in the tables. What I would love to do is this: DECLAR...
T-SQL IsNumeric() and Linq-to-SQL
I need to find the highest value from the database that satisfies a certain formatting convention. Specifically, I would like to fund the highest value that looks like EU999999 ('9' being any digit) select max(col) will return something like 'EUZ...' for instance that I want to exclude. The following query does the trick, but I can...
SQL - Conditional WHERE clause
I have a SQL Server 2005 stored procedure that performs a query. This stored procedure takes in three parameters. The parameters are as follows: @StateID as int, @CountyID as int, @CityID as int These parameters are used to query a list of customers. I want to basically do a "AND" if the parameter value is not null. However, I ...
Using comparison operators in SELECT clause of T-SQL query
How to select a result of comparison operator as a field with type BIT? How it does work in C#: bool isGreater = FieldA > FieldB; How it doesn't work in T-SQL: SELECT (FieldA > FieldB) AS BIT FROM t How to write such task properly? ...
Modify SQL result set before returning from stored procedure
I have a simple table in my SQL Server 2008 DB: Tasks_Table -id -task_complete -task_active -column_1 -.. -column_N The table stores instructions for uncompleted tasks that have to be executed by a service. I want to be able to scale my system in future. Until now only 1 service on 1 computer read from the table. I have a stored proc...
Generate calender
How can I generate the dates for the entire range? Thanks ...
how to create tables using sql schemas.
how to create the schema, AND USING SCHEMA HOW TO create tables under the schemas ...
Creating an audit table in SQL Server not affected by Transaction rollbacks
Hello All, I'm new to SQL. I have a large number of stored procedures in my production database. I planned to write an audit table that would be used by these stored procedures to keep track of changes ( these stored procedures would write to this audit table ). But the issue is that when a transaction rolls back, the rows inserted into...
Check that user belongs to db role
I took some code here: http://stackoverflow.com/questions/2193598/check-if-role-consists-of-particular-user-in-db SELECT * FROM sys.database_role_members AS RM JOIN sys.database_principals AS U ON RM.member_principal_id = U.principal_id JOIN sys.database_principals AS R ON RM.rol...
Need multiple copies of one resultset in sql without using loop
Following is the sample data. I need to make 3 copies of this data in t sql without using loop and return as one resultset. This is sample data not real. 42 South Yorkshire 43 Lancashire 44 Norfolk Edit: I need multiple copies and I have no idea in advance that how many copies I need I have to decide this on the basis of dates. Dat...
sql stored procedure clear resultsets
Can I clear the multiple result sets that a stored procedure has already gathered so that the next result set that is created becomes the very first result set returned by the procedure? ...
T-SQL Script unconsitant in running time. Why?
I've got this script that runs on a SQL Server Express 2008 machine. /* BUD501T */ declare @ErrMsg nvarchar(4000) declare @ErrLine nvarchar(100) declare @currentFY nvarchar(1000) declare @programName nvarchar(1000) set @ErrMsg = '' set @ErrLine = '' set @programName = 'BUDPROD - GL_Exp.sql' select @currentFY = value from Budget...
TSQL: query with optional join
Hi I have a search ui with 3 all optional search criteria. 2 of them are simple criteria for a where statement, that I should be able to solve with this: http://stackoverflow.com/questions/697671/stored-procedure-with-optional-where-parameters. The last criterion is using full text search where I join the result from ContainsTable. Is ...