I’m using Sqlserver express and I can’t do before updated trigger. There’s a other way to do that? Answer MSSQL does not support BEFORE triggers. The closest you have is INSTEAD OF triggers but their behavior is different to that of BEFORE triggers in MySQL. You can learn more about them her…
Tag: sql
How to use a single SqlTransaction for multiple SqlConnections in .NET?
I have SQL Server 2000, it doesn’t support MultipleActiveResults. I have to do multiple inserts, and it’s done with one connection per insertion. I want to begin a transaction before all insertions and finish it after all insertions. How do I do it? Answer What is the reason you don’t use on…
How can I SELECT rows with MAX(Column value), PARTITION by another column in MYSQL?
I have a table of player performance: What query will return the rows for each distinct home holding its maximum value of datetime? In other words, how can I filter by the maximum datetime (grouped by home) and still include other non-grouped, non-aggregate columns (such as player) in the result? For this sam…
What are Covering Indexes and Covered Queries in SQL Server?
Can you explain the concepts of, and relationship between, Covering Indexes and Covered Queries in Microsoft’s SQL Server? Answer A covering index is one which can satisfy all requested columns in a query without performing a further lookup into the clustered index. There is no such thing as a covering …
Difference between JOIN and INNER JOIN
Both these joins will give me the same results: SELECT * FROM table JOIN otherTable ON table.ID = otherTable.FK vs SELECT * FROM table INNER JOIN otherTable ON table.ID = otherTable.FK Is there …
SQL – improve NOT EXISTS query performance
Is there a way I can improve this kind of SQL query performance: INSERT INTO … WHERE NOT EXISTS(Validation…) The problem is when I have many data in my table (like million of rows), the …
efficient way to implement paging
Should I use LINQ’s Skip() and Take() method for paging, or implement my own paging with a SQL query? Which is most efficient? Why would I choose one over the other? I’m using SQL Server 2008, ASP.NET MVC and LINQ. Answer Trying to give you a brief answer to your doubt, if you execute the skip(n).…
Equals(=) vs. LIKE
When using SQL, are there any benefits of using = in a WHERE clause instead of LIKE? Without any special operators, LIKE and = are the same, right? Answer Different Operators LIKE and = are different operators. Most answers here focus on the wildcard support, which is not the only difference between these ope…
Concatenate multiple rows in an array with SQL on PostgreSQL
I have a table constructed like this : I’d like to query this table to get a result like this : I can’t figure a way to do that. Is that possible? How? Answer This is a Postgres built-in since a few versions so you no longer need to define your own, the name is array_agg(). (this is Postgres 8.4.8…
Getting a PDO query string with bound parameters without executing it
Is it possible to get a query string from a PDO object with bound parameters without executing it first? I have code similar to the following (where $dbc is the PDO object): Currently, this will echo out a SQL statement like: “SELECT * FROM users WHERE username = ?”. However, I would like to have …