You use a Microsoft SQL Server 2012 database that contains a table named BlogEntry that has the following
columns:
Id is the Primary Key.
You need to append the “This is in a draft stage” string to the Summary column of the recent 10 entries based
on the values in EntryDateTime. Which Transact-SQL statement should you use?
A.
UPDATE TOP(10) BlogEntry
SET Summary.WRITE(N’ This is in a draft stage’, NULL, 0)
B.
UPDATE BlogEntry
SET Summary = CAST(N’ This is in a draft stage’ as nvarchar(max))
WHERE Id IN(SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC)
C.
UPDATE BlogEntry
SET Summary.WRITE(N’ This is in a draft stage’, NULL, 0) FROM (
SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC) AS s
WHERE BlogEntry.Id = s.ID
D.
UPDATE BlogEntry
SET Summary.WRITE(N’ This is in a draft stage’, 0, 0)
WHERE Id IN(SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC)
Explanation:
Verified answer as correct.
Great post. I used to be checking constantly this weblog and I’m impressed!
Very useful information specially the closing section :
) I handle such info a lot. I used to be seeking this particular info for a very long time.
Thanks and good luck.
0
0
I tried C, but it throws error saying “cannot call method on nvarchar”
Option B works but it is not appending, instead it is overwriting.
The right answer is B but replace the line, Summer = concat(Summary, N’This is in a draft stage’)
0
0
@Kavi
Use nvarchar(max) instead of nvarchar(size) to design the table
0
0
This is a bad question because c and d both work. The difference lies in the word “append”. If append means append to the beginning .write(string,0,0) is the right one. If it means “append to the end” then .write(string,null,0) works. Who needs to know this in real life? No one. The fact that .write only works with nvarchar(max) and not varchar(max) or nvarchar(size) is another intricacy no one uses in real life but they test on us to separate the geeks from the married with children.
0
0
Frank indeed.
For me “append” means add to the end so answer “C”.
I have just one remark. Documentation says
https://msdn.microsoft.com/pl-pl/library/ms177523(v=sql.110).aspx
that varchar(max) is also allowed in expression.
.WRITE (expression,@Offset,@Length)
Specifies that a section of the value of column_name is to be modified. expression replaces @Length units starting from @Offset of column_name. Only columns of varchar(max), nvarchar(max), or varbinary(max) can be specified with this clause.
0
0
.WRITE (expression,@Offset,@Length):
Specifies that a section of the value of column_name is to be modified. expression replaces @Length units starting from @Offset of column_name. …
expression: is the value that is copied to column_name. … If expression is set to NULL, @Length is ignored, and the value in column_name is truncated at the specified @Offset.
@Offset: is the starting point in the value of column_name at which expression is written. … If @Offset is NULL, the update operation appends expression at the end of the existing column_name value and @Length is ignored. …
0
0
D is the correct answer end of discussion.
0
0
https://sqlwithmanoj.com/2011/10/04/update-statement-with-new-write-clause/
0
0
If @Offset is NULL, the update operation appends expression at the end of the existing column_name value and @Length is ignored
C
1
0
i tested it,i promise it.the answer C and D are both work,but they has a little difference.
answer C will add the string forward the value,
answer D will add the string behind the value.
——this is my demo
if object_id(‘testdb1..a’) is not null
drop table a
create table a(id int,a varchar(max))
insert into a
select 1,’abcdefghijklmnopqrstuvwxyz~!~1234567890′
union all
select 2,’aaaaaaaa’
union all
select 3,’bbbbbbbb’
if object_id(‘testdb1..b’) is not null
drop table b
create table b(id int)
insert into b
select 1 union all
select 2 union all
select 3
–UPDATE a
–SET a.WRITE(N’This is in a draft stage’, NULL, 0) FROM (
–SELECT TOP(2) Id FROM b ORDER BY id DESC) AS s
–WHERE a.Id = s.ID
UPDATE a
SET a.WRITE(N’This is in a draft stage’, 0, 0)
WHERE Id IN(SELECT TOP(2) Id FROM b ORDER BY id DESC)
select * from a
0
0
i glad to choose D,it’s easier to understand
0
0
Besides, SOME new 200Q 70-461 dumps are available here:
https://drive.google.com/open?id=0B-ob6L_QjGLpfnJldlZxTklTaHM0akpJUzhja2pETHJOS0owMzd4eVk1UTVNQUpvdlVxVWM
Best Regards!
0
0