SQL
SQL
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 constraint in the database that is named something like “DF__test__col1__182C9B23,” which is pretty ugly. The last several characters are some sort of random-ish sequence, and will be different on everybody’s database. The difficulty comes later if some needs to modify this table:
alter table test drop column col1
You’ll get the error:
The object 'DF__test__col1__182C9B23' is dependent...
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: with xmlnamespaces(
'http://mydomain.com/person/extension/0.1' as px)
update People
set XmlColumn.modify('delete //px:PersonAlternateName')
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 PersonAlternativeNames in a single result set use CROSS APPLY or OUTER APPLY. with xmlnamespaces(
'http://mydomain.com/person/extension/0.1' as px,
'http://niem.gov/niem/niem-core/2.0' as n)
select p.PersonId,
n.value('(n:PersonGivenName)[1]', 'varchar(100)') as FN,
n.value('(n:PersonMiddleName)[1]', 'varchar(100)') as MN,
n.value('(n:PersonSurName)[1]', 'varchar(100)') as LN
from People p
cross apply XmlColumn.nodes('//px:PersonAlternateName') as ppl(n)