Generate SQL Create Scripts with Query
I need to be able to get the CREATE scripts for existing tables within SQL Server 2008. I assume I can do this by querying the sys.tables somehow, however this isn't returning me the CREATE script data. ...
sql
What is the Auto-Close option when creating a new database in SQL Server 2008?
What is the Auto-Close option when creating a new database in SQL Server 2008? EDIT: and how do you decide whether to turn it on or off? ...
Fastest way to format SQL results in PHP
What's the fastest (best performing) way in PHP to transform a (My)SQL result like: array( array('user_name' => 'john', 'tag_id' => 1, 'tag_name' => 'foo'), array('user_name' => 'john', 'tag_id' => 2, 'tag_name' => 'bar'), array('user_name' => 'rick', 'tag_id' => 3, 'tag_name' => 'foobar'), array('user_name' => 'rick', 'tag_id' => 2...
Difference between CTE and SubQuery?
From this post How to use ROW_NUMBER in the following procedure? There are two versions of answers where one uses a SubQuery and the other uses a CTE to solve the same problem. Now then, what is the advantage of using a CTE (Common Table Expression) over a sub-query(thus, more readable what the query is actually doing) The only advant...
How do I select rows from a MySQL table grouped by one column with required values in another
I need to filter products where certain attributes are stored in a joined table that match all the required properties, i.e. users need to be able to gradually narrow down their search by adding requirements. The problem really just concerns the properties table I think, rather than the join, given the following (simplified) table of p...
Quick way to backup SQL SP and Functions?
I have a long list of SPs (stored procedure) and Functions in my SQL server db. I could save them one by one by right clicking and script XXX to Alter To. Is there any way in TSQL to query all SPs and functions save them to xxx.sql files? For example, for sp_mySP1, I would like to save it to sp_mySP1.sql which is a text file. The databa...
T-SQL Cast versus Convert
What is the general guidance on when you should use CAST versus CONVERT? Is there any performance issues related to choosing one versus the other? Is one closer to ANSI-SQL? ...
SQL UPDATE SET one column to be equal to a value in a related table referenced by a different column?
I hope that made sense, let me elaborate: There is a table of tracking data for a quiz program where each row has.. QuestionID and AnswerID (there is a table for each). So because of a bug there were a bunch of QuestionIDs set to NULL, but the QuestionID of a related AnswerID is in the Answers table. So say QuestionID is NULL and Answ...
SQL Unique Record not column?
Is there a way to insert into an SQL database where the whole record is unique? I know you can make primary keys and unique columns, but that is not what I want. What is the best way of doing this without overloading the database? I have seen a sort of subquery where you use "WHERE NOT EXISTS ()" I just want to know the most efficient ...
MS Access interop - Data Import
I am working on a exe to export SQL to Access, we do not want to use DTS as we have multiple clients each exporting different views and the overhead to setup and maintain the DTS packages is too much. *Edit: This process is automated for many clients every night, so the whole process has to be kicked off and controlled within a cursor i...
Most portable SQL date string format
Across various SQL dialects, what string representation for a Date and/or DateTime would be most likely to be interpreted correctly? Is there an SQL Standard? Are the two answers identical or similar? EDIT: For suggestions, can we all please comment with any known SQL dialects that don't comply? ...
How Important is SQL Portability?
It seems to me, from both personal experience and SO questions and answers, that SQL implementations vary substantially. One of the first issues for SQL questions is: What dbms are you using? In most cases with SQL there are several ways to structure a given query, even using the same dialect. But I find it interesting that the relative...
Get records ordered alphabetically starting by a certain letter in Sql Server
In SQLSERVER/MSSQL, here's the problem: SELECT * from [Translation Color] order by [Language Code] I want records ordered in alphabetical order starting by the 'I' letter. Example of result: 'Ioren' 'Iumen' 'Tart' 'Arfen' 'Coldry' I don't want to use union or more sql statements.. just try to catch it with an order by special claus...
Restore sql doesnt fire
Hi Im trying restore database from backup dynamically with application code simple sql command for restore con.execute("RESTORE FILELISTONLY FROM DISK='c:\old.bak' " & vbcrlf &_ "RESTORE DATABASE newdb " & vbcrlf &_ "FROM DISK='c:\old.bak' " & vbcrlf &_ "WITH MOVE 'newdb' TO 'c:\newdb.mdf', " & vbcrlf &_ ...
how to write sql query? for ms sql server
I need to write a sql query that adds one column from one database (DB1) to another column and the sum is save in that column in the second database(DB2). where userIds are the same DB1 TableA UserId People DB2 TableB Amount UserId it would be something like this DB2.TableB.Amount = DB2.TableB.Amount + DB1.TableA.People ...
MSSQL Cursor for huge tables cannot allocate space
We are trying to set up a cursor to run through records generated from a join between two 'instances' of the same huge table (more than 150 M records). The following exception message comes out: Could not allocate space for object 'dbo.SORT temporary run storage: 165282123350016' in database 'tempdb' because the 'PRIMARY' filegroup...
What does it mean by "Non-deterministic User-Defined functions can be used in a deterministic manner"?
According to MSDN SQL BOL (Books Online) page on Deterministic and Nondeterministic Functions, non-deterministic functions can be used "in a deterministic manner" The following functions are not always deterministic, but can be used in indexed views or indexes on computed columns when they are specified in a deterministic manner. ...
Can I store SQL Server sort order in a variable?
I have the following SQL within a stored procedure. Is there a way to remove the IF statement and pass the 'ASC'/'DESC' option as a variable? I know I could do the query a number of different ways, or return a table and sort it externally etc. I would just like to know if I can avoid duplicating the CASE statement. IF @sortOrder = 'Des...
How can I select distinct rows when a text field is part of the returned fields in MS SQL Server 2000?
I have a database-driven FAQ that is organised into sections and I am trying to get the section data for only those sections who have a question/answer associated with them. Here is the schema: |---------------------| |----------------------| | Section | | Quest-Ans | |---------------------| |-----...
There is a way to access the "previous row" value in a SELECT statement?
I need to calculate the difference of a column between two lines of a table, there is any way I can do this directly in SQL? I'm using Microsoft SQL Server 2008. I'm looking for something like this: SELECT value - (previous.value) FROM table Imagining that the "previous" variable reference the latest selected row. Of course that with...