January 13, 2020 at 7:19 pm
In our environment we have columns which have null values in the column we want to partition on.
do you think having null values if okay? or is there any best practice to make it as not null.
Thanks
January 13, 2020 at 8:19 pm
Have you looked in BOL - LINK
From a quick google (as we don't use partitioning and it has been ages since I last looked at partitioning), null values are acceptable but may not work the way you expect them to. BOL indicates that "Any rows whose partitioning column has null values are placed in the left-most partition unless NULL is specified as a boundary value and RIGHT is indicated. In this case, the left-most partition is an empty partition, and NULL values are placed in the following partition." If that is your build design, then it is fine. If that isn't what you want to happen, you may want to put a non-null value in instead of null.
As for "best practice" to make a null, not null, I do not believe there is one nor could there be. Your system may be designed so "N/A" is your "null" value, or you may want the string literal "null", not the NULL as seen by SQL Server. Or it may be a numeric column in which case "N/A" would fail. With all the different possible datatypes that could go into the column, having a "best practice" doesn't make sense. It depends on what you have in there. What we do is pick a value that should not exist in that column ("na", -1, etc) and assign that as our "unassigned" value where necessary. I personally try to avoid using NULL wherever possible as it behaves differently than an actual value. For example, having a where clause like "WHERE value = NULL" will give me no results even if value is a nullable column AND has NULL values. Or even "where value1 = value2". But there are cases where an application may expect or require a NULL value.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 13, 2020 at 11:35 pm
Does table partitioning supported with the JSON blob structures?
January 16, 2020 at 2:11 pm
Admingod - first, do not hijack posts. Your question is not related to the OP except for the fact that it is about partitioning.
Second, did you read the post I linked from BOL? JSON is not a valid data type in SQL Server. If you want to use JSON, you are storing it in a different datatype, most likely NVARCHAR(MAX). NVARCHAR(MAX) is not allowed for partitioning as a partitioning column.
Now, if the table CONTAINS NVARCHAR(MAX) column but it is not used as the partitioning column, that is allowed.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply