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)
append
update .write
0
0
???
you can choose only one, the correct is: C
0
0
Is this syntax acceptable in SQL Server?
0
0
B
I checked
0
0
you checked badly!
0
0
“Cannot call methods on nvarchar.” so the .write does not work.
Only C doean’t use that method
In the question it states “You need to append the ” so I would change
SET Summary = CAST(N’ This is in a draft stage’ as nvarchar(max))
to
SET Summary = CAST(N’ This is in a draft stage’ as nvarchar(max)) + summary
0
0
I meant B not C 🙂
0
0
Sorry but you are wrong C is the write answer and works fine!
https://sqlwithmanoj.com/2011/10/04/update-statement-with-new-write-clause/
0
0
*right 😀
0
0
B is right answer – I checked
0
0
Another person that cannot “check”, B Overwrites the text, it does not append as the question requests. C is correct, proof below in my post, i REALLY CHECKED
0
0
C is right.
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. Only columns of varchar(max), nvarchar(max), or varbinary(max) can be specified with this clause. column_name cannot be NULL and cannot be qualified with a table name or table alias.
expression is the value that is copied to column_name. expression must evaluate to or be able to be implicitly cast to the column_name type. 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. @Offset is a zero-based ordinal position, is bigint, and cannot be a negative number. If @Offset is NULL, the update operation appends expression at the end of the existing column_name value and @Length is ignored. If @Offset is greater than the length of the column_name value, the Database Engine returns an error. If @Offset plus @Length exceeds the end of the underlying value in the column, the deletion occurs up to the last character of the value. If @Offset plus LEN(expression) is greater than the underlying declared size, an error is raised.
@Length is the length of the section in the column, starting from @Offset, that is replaced by expression. @Length is bigint and cannot be a negative number. If @Length is NULL, the update operation removes all data from @Offset to the end of the column_name value.
0
0
https://msdn.microsoft.com/en-us/library/ms177523.aspx
0
0
Tested and The correct answer is C.
0
0
if u use statment from answer B u will just overwrite data in Summary.
Answer C is correct, it append text on existing text in column.
0
0
C
0
0
Is answer D provide the same the result as C? Looks like it does. Why it is not right?
0
0
D Puts the new text at the beginning of the existing text, the question asks you to append (put at the end).
0
0
See my full test below for explanations and code to prove
0
0
BTW, part of the new 200Q 70-461 dumps FYI:
https://drive.google.com/open?id=0B-ob6L_QjGLpfnJldlZxTklTaHM0akpJUzhja2pETHJOS0owMzd4eVk1UTVNQUpvdlVxVWM
Best Regards!
0
0
CREATE TABLE BlogEntry
(
Id BIGINT,
EntryDateTime DATETIME,
Summary NVARCHAR(MAX)
)
INSERT INTO BlogEntry(Id,EntryDateTime,Summary)
VALUES(1,GETDATE()-11,’One’);
INSERT INTO BlogEntry(Id,EntryDateTime,Summary)
VALUES(2,GETDATE()-10,’Two’);
INSERT INTO BlogEntry(Id,EntryDateTime,Summary)
VALUES(3,GETDATE()-9,’Three’);
INSERT INTO BlogEntry(Id,EntryDateTime,Summary)
VALUES(4,GETDATE()-8,’Four’);
INSERT INTO BlogEntry(Id,EntryDateTime,Summary)
VALUES(5,GETDATE()-7,’Five’);
INSERT INTO BlogEntry(Id,EntryDateTime,Summary)
VALUES(6,GETDATE()-6,’Six’);
INSERT INTO BlogEntry(Id,EntryDateTime,Summary)
VALUES(7,GETDATE()-5,’Seven’);
INSERT INTO BlogEntry(Id,EntryDateTime,Summary)
VALUES(8,GETDATE()-4,’Eight’);
INSERT INTO BlogEntry(Id,EntryDateTime,Summary)
VALUES(9,GETDATE()-3,’Nine’);
INSERT INTO BlogEntry(Id,EntryDateTime,Summary)
VALUES(10,GETDATE()-2,’Ten’);
INSERT INTO BlogEntry(Id,EntryDateTime,Summary)
VALUES(11,GETDATE()-1,’Eleven’);
–A (Updates text but not for most recent 10 by date (Id’s 11-2))
UPDATE TOP(10) BlogEntry
SET Summary.WRITE(N’ This is in draft stage’,NULL,0)
–B (Edits correct records but overwrites existing text, does not append)
UPDATE BlogEntry
SET Summary = CAST(N’ This is in draft stage’ as nvarchar(max))
WHERE Id IN(SELECT TOP(10) Id
FROM BlogEntry ORDER BY EntryDateTime DESC)
–C (CORRECT, NULL FORCES AN APPEND)
UPDATE BlogEntry
SET Summary.WRITE(N’ This is in draft stage’,NULL,0) FROM
(SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC) AS s
WHERE BlogEntry.Id = s.Id
–D (Updates correct records but adds text to the beginning not the end)
UPDATE BlogEntry
SET Summary.WRITE(N’ This is in draft stage’,0,0)
WHERE Id IN (SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC)
SELECT * FROM BlogEntry
GO;
1
0
…badly because B overwrites, it does not append
0
0
Answer is C
0
0