Microsoft reserves the right to change the binary representation of the build-in data types like DATETIME
. So if you do an
INSERT dbo.table(binaryColumn)VALUES(CAST(GETDATE() AS VARBINARY(8)));
and then you upgrade SQL Server or even do an SP apply
SELECT CAST(binaryColumn AS DATETIME) FROM dbo.table;
might get you a different date then the one originally stored.
However, if you store you own binary data that was generated outside of SQL Server (e.g. a pdf file), that data will not be affected by such a change.
This by the way is not new. Microsoft always said that they might change the binary representation at any time. They might just have made it more obvious in the new BOL version.
That all being said, it is still very unlikely that Microsoft is going to change the binary representation of say an integer. However, something like a geospatial, which is implemented as a CLR type, is very likely to change between versions.
As per Paul's comment: SQL Server does indeed guarantee that you can convert a build-in data type to binary and back within the same statement. To extend that to the same session when storing the intermediate value in tempdb however I would already consider risky - a very small risk, but a risk. Storing the intermediate value in a real table to pick it back up at a later time is a definite no-no.
COUNT(*) Returns all rows ignoring nulls right?
I'm not sure what you mean by "ignoring nulls" here. It returns the number of rows irrespective of any NULL
s
SELECT COUNT(*)
FROM (VALUES (CAST(NULL AS INT)),
(CAST(NULL AS INT))) V(C)
Returns 2
.
Altering the above query to COUNT(C)
would return 0
as when using COUNT
with an expression other than *
only NOT NULL
values of that expression are counted.
Suppose the table in your question has the following source data
+---------+---------------+
| Name | MaritalStatus |
+---------+---------------+
| Albert | Single |
| Bob | Single |
| Charles | Single |
| David | Single |
| Edward | Married |
| Fred | Married |
| George | NULL |
+---------+---------------+
The query
SELECT MaritalStatus,
COUNT(*) AS CountResult
FROM T
GROUP BY MaritalStatus
Returns
+---------------+-------------+
| MaritalStatus | CountResult |
+---------------+-------------+
| Single | 4 |
| Married | 2 |
| NULL | 1 |
+---------------+-------------+
Hopefully it is obvious how that result relates to the original data.
What does COUNT(*) OVER()
do?
Adding that into the SELECT
list for the previous query produces
+---------------+-------------+-----------------+
| MaritalStatus | CountResult | CountOverResult |
+---------------+-------------+-----------------+
| Single | 4 | 3 |
| Married | 2 | 3 |
| NULL | 1 | 3 |
+---------------+-------------+-----------------+
Notice that the result set has 3 rows and CountOverResult is 3. This is not a coincidence.
The reason for this is because it logically operates on the result set after the GROUP BY
.
COUNT(*) OVER ()
is a windowed aggregate. The absence of any PARTITION BY
or ORDER BY
clause means that the window it operates on is the whole result set.
In the case of the query in your question the value of CountOverResult
is the same as the number of distinct MaritalStatus
values that exist in the base table because there is one row for each of these in the grouped result.
Best Answer
Sounds like you are not asking about XML itself, but about the XQuery/XPath/XSLT data model known as XDM.
In these languages there are two kinds of value: nodes, which basically means anything within an XML document, and atomic values like integers and strings which have no relationship to any XML document. So for example the query count(//employee) takes a set of nodes as input, and produces an atomic value (an integer) as output. An important operation is atomization, which extracts an atomic value from a node, for example the string that is the value of an attribute node.