What is the correct way to do this? For example, how would I change a stored procedure with this signature: CREATE PROCEDURE dbo.MyProcedure @Param BIT = NULL AS SELECT * FROM dbo.SomeTable T WHERE T.SomeColumn = @Param So that giving @Param with a value of 1 or 0 performs the filter, but not specifying it or passing NULL perf...
The SQL implementation of relational databases has been around in their current form for something like 25 years (since System R and Ingres). Even the main (loosely adhered to) standard is ANSI-92 (although there were later updates) is a good 15 years old. What innovations can you think of with SQL based databases in the last ten years...
I am trying to use concat_ws inside a group_concat command. With a query, which simplified looks like: SELECT item.title, GROUP_CONCAT( CONCAT_WS( ',', attachments.id, attachments.type, attachments.name ) ) as attachments FROM story AS item LEFT OUTER JOIN story_attachment AS attachments ON item.id = attachments.item_id GROUP BY ...
Shouldn't this be a pretty straightforward operation? However, I there is no size() or length() method. ...
I need specifically to load a JPG image that was saved as a blob. GDI+ makes it very easy to retrieve images from files but not from databases... ...
Assume you have a flat table that stores an ordered tree hierarchy: Id Name ParentId Order 1 'Node 1' 0 10 2 'Node 1.1' 1 10 3 'Node 2' 0 20 4 'Node 1.1.1' 2 10 5 'Node 2.1' 3 10 6 'Node 1.2' 1 20 What minimalistic appro...
I am selecting from a table that has an XML column using T-SQL. I would like to select a certain type of node and have a row created for each one. For instance, suppose I am selecting from a people table. This table has an XML column for addresses. The XML is formated similar to the following: <address> <street>Street 1</street> <c...
I am trying to create a recordset in Access VBA that will show me all records in a table related to the current record of a form. My current code looks like this: Private Sub Form_Load() Dim rst As Recordset Set rst = CurrentDb.OpenRecordset("Select [ID], [Ln] From [Order Detail] Where ((([Order Detail].[ID]) = [Forms]![Order...
Can you get the distinct combination of 2 different fields in a database table? if so, can you provide the SQL example. ...
SQL Server (2005/2008) Each of the below statements have the same result. Does anyone know if one outperforms the other? insert into SOMETABLE values ('FieldOneValue','FieldTwoValue',3,4.55,'10/10/2008 16:42:00.000') insert into SOMETABLE select 'FieldOneValue','FieldTwoValue',3,4.55,'10/10/2008 16:42:00.000' insert into SOMETA...
Assuming a Read Committed Snapshot transaction isolation setting, is the following statement "atomic" in the sense that you won't ever "lose" a concurrent increment? update mytable set counter = counter + 1 I would assume that in the general case, where this update statement is part of a larger transaction, that it wouldn't be. For exa...
How are non-deterministic functions used in SQL partitioning functions and are they useful? ...
I have some data records that may or may not exist already in my database. Right now when I want to update these records, I have to keep track of whether I got them from the DB, or created them from scratch, so that I know whether to make an INSERT or UPDATE query. I could query the database just before writing to find out if the recor...
Is there any smart way to do this? If using Linq2Nhibernate, you really seem to have to rely on HQL or the likes to do multiple deletes from a database (without loading up and deleting one by one)? It doesn't seem like Linq2Sql have it either? I just want something that can do stuff like: DELETE FROM Accounts WHERE amount < 1000 Any...
Say you have a table Names that has three rows: Peter Paul Mary Is there an easy way to turn this into a single string of "Peter, Paul, Mary"? ...
When you run something similar to: UPDATE table SET datetime = NOW(); on a table with 1 000 000 000 records and the query takes 10 seconds to run, will all the rows have the exact same time (minutes and seconds) or will they have different times? In other words, will the time be when the query started or when each row is updated? I'm...
I have a table with two fields of interest for this particular exercise: a CHAR(3) ID and a DATETIME. The ID identifies the submitter of the data - several thousand rows. The DATETIME is not necessarily unique, either. (The primary keys are other fields of the table.) Data for this table is submitted every six months. In December, w...
I've got some LINQ to SQL that sometimes throws a "Cannot insert duplicate key row in object 'dbo.Table' with unique index 'IX_Indexname'.The statement has been terminated." Is there some way I can turn on logging or at least debug into the datacontext to see what sql is being executed at the time that error is raised? Update: ...
Would you recommend me the best approach to edit SQL query with Visual Studio 2008 Professional, please? I know I can open Query window from context menu in Server Explorer and edit text in SQL Pane. But unfortunately I am not allowed to save query to a file and Find and Replace commands are not working there. Alternatively I can open...
In a SQL-database I make some selects, that get an duration (as result of a subtraction between two dates) in seconds as an int. But I want to format this result in a human-readable form like 'hh:mm' or 'dd:hh'. Is that possible in SQL and how can I realize this? ...