Category Archives: SQL

SQL Snapshot Notes

Sometimes when testing you need to repetitively restore a database back to a certain point in time. For very large databases the regular SQL restore can take quite a while. It seemed to me that using snapshots instead of regular … Continue reading

Posted in CodeMinder, SQL | Leave a comment

Force a SQL Restore

Really tired of having to google this and piece it together every time I want it, so I’m posting it here. <span class="kwrd">alter</span> <span class="kwrd">database</span> DbName <span class="kwrd">set</span> single_user <span class="kwrd">with</span> <span class="kwrd">rollback</span> <span class="kwrd">immediate</span> <span class="kwrd">go</span> <span class="kwrd">restore</span> <span … Continue reading

Posted in CodeMinder, SQL | Leave a comment

Update a SQL table’s XML value to insert a node

Sometimes a snippet of example code is all that’s needed: <span class="kwrd">update</span> MyTable <span class="kwrd">set</span> XmlColumn.<span class="kwrd">modify</span>(<span class="str">' insert &lt;Description&gt;This is a description&lt;/Description&gt; as last into (/xpath/to/parent)[1] '</span>) <span class="kwrd">where</span> SomeValue = <span class="str">'whatever'</span>

Posted in CodeMinder, Programming, SQL, XML | Leave a comment

SQL Injection Joke (with a lesson)

At a recent team stand up meeting I passed along a joke I caught on twitter:   A SQL Injection walks into a bar, starts to quote something but stops, drops a table, then dashes out. The joke is a … Continue reading

Posted in Programming, SQL | Leave a comment

Drop a SQL column default constraint that has been implicitly named

CodeMinder: to remind myself of stuff that I usually have to look up each time I do it. Notice the unnamed default in the following line: alter table test add col1 int not null default 1 This creates a default … Continue reading

Posted in CodeMinder, Programming, SQL | Leave a comment

Update a SQL table’s XML value to remove nodes

CodeMinder: to remind myself of stuff that I usually have to look up each time I do it. To delete all PersonAlternativeName nodes from XmlColumn of the People table: <span style="color: #0000ff;">with</span><span style="color: #000000;"> xmlnamespaces( </span><span style="color: #ff0000;">'</span><span style="color: #ff0000;">http://mydomain.com/person/extension/0.1</span><span … Continue reading

Posted in CodeMinder, Programming, SQL, XML | Leave a comment

Select multiple XML nodes out of each row in a SQL table

CodeMinder: to remind myself of stuff that I usually have to look up each time I do it. If a People table has a column named XML, and the XML column may specify multiple PersonAlternativeName entities, then to get all … Continue reading

Posted in CodeMinder, SQL, XML | Leave a comment