What happens if you don't commit transaction in a database (say SQL Server)

Suppose I have a query: begin tran — some other sql code and then I forget to commit or roll back. If another genuine client tries to execute a query, what would happen?

SQL Server UNION – What is the default ORDER BY Behaviour

If I have a few UNION Statements as a contrived example: SELECT * FROM xxx WHERE z = 1 UNION SELECT * FROM xxx WHERE z = 2 UNION SELECT * FROM xxx WHERE z = 3 What is the default order by behaviour? The test data I’m seeing essentially does not return the data […]

Is it possible to run multiple DDL statements inside a transaction (within SQL Server)?

I’m wondering if it is possible to run multiple DDL statements inside a transaction. I’m specially interested on SQL Server, even though answers with other databases (Oracle, PostgreSQL at least) could also be interesting. I’ve been doing some “CREATE TABLE” and “CREATE VIEW” for the created table inside a transaction and there seems to be […]

Solution for: Store update, insert, or delete statement affected an unexpected number of rows (0)

I found a solution for people who get an exception: Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. Refresh ObjectStateManager entries. But, anyway I have question. I read topic: Entity Framework: "Store update, insert, or delete statement affected an […]

How to setup the maxrecursion option for a CTE inside a Table-Valued-Function

I’m facing a problem to declare the maxrecursion option for a CTE inside a TVF here is the CTE (a simple calendar) : DECLARE @DEBUT DATE = ‘1/1/11’, @FIN DATE = ‘1/10/11’; WITH CTE as( SELECT @debut as jour UNION ALL SELECT DATEADD(day, 1, jour) FROM CTE WHERE DATEADD(day, 1, jour) <= @fin) SELECT jour […]

Need to list all triggers in SQL Server database with table name and table's schema

I need to list all triggers in SQL Server database with table name and table’s schema. I’m almost there with this: SELECT trigger_name = name, trigger_owner = USER_NAME(uid),table_schema = , table_name = OBJECT_NAME(parent_obj), isupdate = OBJECTPROPERTY( id, ‘ExecIsUpdateTrigger’), isdelete = OBJECTPROPERTY( id, ‘ExecIsDeleteTrigger’), isinsert = OBJECTPROPERTY( id, ‘ExecIsInsertTrigger’), isafter = OBJECTPROPERTY( id, ‘ExecIsAfterTrigger’), isinsteadof = […]

Forcing a query timeout in SQL Server

We have had an issue with a block of code that responds poorly in the face of slow databases (It craps the bed on a query timeout). We have created a patch, and are in the process of running it through regression. We can’t get a timeout. I’ve opened a transaction from SQL Mgmt Studio […]

T-SQL Skip Take Stored Procedure

I don’t seem to be having much luck on this site, still forever the optimist, I will keep trying. I have two tables, Journals and ArticleCategories that are joined using the this query: SELECT Journals.JournalId, Journals.Year, Journals.Title, ArticleCategories.ItemText FROM Journals LEFT OUTER JOIN ArticleCategories ON Journals.ArticleCategoryId = ArticleCategories.ArticleCategoryId Can anyone tell me how I can […]

What columns generally make good indexes?

As a follow up to “What are indexes and how can I use them to optimise queries in my database?” where I am attempting to learn about indexes, what columns are good index candidates? Specifically for an MS SQL database? After some googling, everything I have read suggests that columns that are generally increasing and […]

SQL server schema and default schema

I have a schema define in my database. Except now everytime I do a sql statement I have to provide the schema … SELECT * FROM [myschema].table I set the default schema for my user using management studio and also ran the ALTER USER myUser WITH DEFAULT_SCHEMA [myschema] and I still get the invalid object […]

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