I was looking at postgres question and it called for updating a table with random values. I only noticed it was targeted at postgres after answering it, but in answering it I hit a mystery. Here is my sample code in question: create table #Buildings([Use] varchar(50), n int) insert #Buildings select null,null from sysobjects upda...
I am running a market survey and all the survey data is saved in the database. I need a query for counting the number of rows in which option "1" is selected for question "1", option "2" for question "1" and so on for all questions and options… I need to specify few conditions here, I have to match distinct ID's of 3 tables and display t...
Here is the situation: I have a table value function with a datetime parameter ,lest's say tdf(p_date) , that filters about two million rows selecting those with column date smaller than p_date and computes some aggregate values on other columns. It works great but if p_date is a custom scalar value function (returning the end of day in ...
Hi I am using asp .net web form search in c#, for example with fields: - name - fruit I want to write a sproc that can search name or fruit. If I knew that both would have values, I would pass two parameters into the sproc and would write: Select * from myTable where name =@name and fruit = @fruit However sometimes, either name or f...
I am working on an old database I inherited from my predecessors. In it, some float fields contains NaN where there should be a null. The following SQL doesn't work because it doesn't recognize NaN. UPDATE xxx SET column= null WHERE column=NaN How can I do this? ...
I am trying to test a proof of concept that I can run a distributed transaction across two linked SQL Servers, linked using sp_addlinkedserver - their names are Server1 and Server2, both running under default instances. Each server holds a single database, Source and Destination respectively and the destination database holds a single t...
I am trying to build an SQL Statement for the following search scenario: I have trying to return all of the columns for an individual record for Table A based on the value of the status column in Table B. Each record in table A can have multiple rows in table B, making it a one to many relationship. The status column is nullable with ...
I am trying to do a nice SQL statement inside a stored procedure. I looked at the issue of seeing the number of days that events happened between two dates. My example is sales orders: for this month, how many days did we have sales orders? Suppose this setup: CREATE TABLE `sandbox`.`orders` ( `year` int, `month` int, `day` int...
Hi, I am new to t-sql. Can someone tell me how to fetch records from a sql table in the same order WITHOUT using the order by clause? ...
Given a table-valued function such as dbo.Split() from "T-SQL: Opposite to string concatenation - how to split string into multiple records", how do I pass multiple rows as arguments? This works: SELECT * FROM dbo.Split (',', (SELECT myColumn FROM Stuff WHERE id = 22268)) WHERE ISNULL(s,'') <> '' It returns: pn s --------...
I want to return the results of select Column from Table into a comma separated string using SQL Server. The column in question is rather large (nvarchar(2000)) so the solution has to be able to handle very large result values. ...
What is the exact SQL to assign db_datareader and db_datawriter roles to a user in SQL Server? Say the user name is MYUSER and the DB in question is MYDB. Thanks! ...
I am trying to remove part of a string from another string, for example: declare @url varchar (20) set @url = 'www.test.com~~34235645463544563554' select @url, substring(@url,1,CHARINDEX('~~',@url)-1) I am trying to remove '~~34235645463544563554' I am use to using the built in tsql functions (as shown above) to do this but trying t...
I would like to randomly sort a result in a repeatable fashion for purposes such as paging. For this NEWID() is too random in that the same results cannot be re-obtained. Order by Rand(seed) would be ideal as with the same seed the same random collection would result. Unfortunately, the Rand() state resets with every row… does anyone ...
Assume the Stored Proc takes 10 minutes to run and returns no data. What is the proper way to call a stored procedure in SQL Server and have your code not wait around for the result? Is there a way to handle it from the T-SQL or the connection? Something that works in ADO.NET and classic ActiveX ADO? The only way I thought of is: 1) Cr...
For a large database (thousands of stored procedures) running on a dedicated SQL Server, is it better to include SET NOCOUNT ON at the top of every stored procedure, or to set that option at the server level (Properties -> Connections -> "no count" checkbox)? It sounds like the DRY Principle ("Don't Repeat Yourself") applies, and the opt...
I have a bunch (750K) of records in one table that I have to see they're in another table. The second table has millions of records, and the data is something like this: Source table 9999-A1B-1234X, with the middle part potentially being longer than three digits Target table DescriptionPhrase9999-A1B-1234X(9 pages) - yes, the parens a...
I'm just reading my company's code guidelines and it says to never treat variables as a constant in sql server, use literals instead. The reasoning is that sql server can't build a good execution plan when you're using variables in the query. Anybody know if this is still true? We're using MSSQL 2005 and this document may have been wr...
What is a stored procedure? How do they work? What is the make-up of a stored procedure (things each must have to be a Stored Procedure)? ...
I want to create a persisted computed column in a table so that I can use it as part of a foreign-key relationship to another table. I have these tables: Events (EventID uniqueidentifier, EventCode varchar(8)) -- EventCode is a discriminator column Parties (EventID uniqueidentifier) ... and I want to add an EventCode column to "Part...