using (var Conn = new SqlConnection(_ConnectionString)) { SqlTransaction trans = null; try { Conn.Open(); trans = Conn.BeginTransaction(); using (SqlCommand Com = new SqlCommand(ComText, Conn, trans)) { /* DB work */ } trans.Commit(); } catch (Exception Ex) { if (trans != null) trans.Rollback(); return -1; } } or you could go even cleaner and easier and … Read more
You have to swap the TRUNCATE statement to DELETE statements, slower and logged but that’s the way to do it when constraints are in place. DELETE mytablename; Either that or you can find the foreign keys that are referencing the table in question and disable them temporarily. select ‘ALTER TABLE ‘||TABLE_NAME||’ DISABLE CONSTRAINT ‘||CONSTRAINT_NAME||’;’ from … Read more
If you are using Django 1.1 beta (trunk): Player.objects.values(‘player_type’).order_by().annotate(Count(‘player_type’)) values(‘player_type’) – for inclusion only player_type field into GROUP BY clause. order_by() – for exclusion possible default ordering that can cause not needed fields inclusion in SELECT and GROUP BY.
GUIDs are problematic as clustered keys because of the high randomness. This issue was addressed by Paul Randal in the last Technet Magazine Q&A column: I’d like to use a GUID as the clustered index key, but the others are arguing that it can lead to performance issues with indexes. Is this true and, if … Read more
Your UPDATE query should look like this: UPDATE table2 t2 SET val2 = t1.val1 FROM table1 t1 WHERE t2.table2_id = t1.table2_id AND t2.val2 IS DISTINCT FROM t1.val1; — optional, see below The way you had it, there was no link between individual rows of the two tables. Every row would be fetched from table1 for … Read more
If droid_v can only be 0, 1, 2, 3, or 4, then COUNT(DISTINCT) will never return more than 5, since there are only five possible values. Is that what you want? If so, then try this: SELECT puid, COUNT(DISTINCT CASE WHEN droid_v > 0 THEN droid_v ELSE 0 END) – 1 AS droid /* -1 … Read more
In SQL Server pre-2017, you can do: select stuff( (select ‘,’ + cast(t.id as varchar(max)) from tabel t for xml path (”) ), 1, 1, ” ); The only purpose of stuff() is to remove the initial comma. The work is being done by for xml path.
Good on you, for eliminating Nulls. I have never allowed Nulls in any of my databases. Of course, if nulls are prohibited, then missing information will have to be handled by some other means. Unfortunately, those other means are much too complex to be discussed in detail here. Actually it is not so hard at … Read more
If you are on SQL Server 2016+ You can use the new STRING_SPLIT function, which I’ve blogged about here, and Brent Ozar has blogged about here. SELECT s.[message-id], f.value FROM dbo.SourceData AS s CROSS APPLY STRING_SPLIT(s.[recipient-address], ‘;’) as f; If you are still on a version prior to SQL Server 2016 Create a split function. … Read more
Arranging proper cascading deletes is wise and is usually the correct solution to this. For certain special cases, there is another solution to this that can be relevant. If you need to perform multiple deletes based on a common set of data you can use Common Table Expressions (CTE). It’s hard to come up with … Read more