Tag Info

New answers tagged

0

There is no advantage for the non-NULL cases when using SPARSE, and in fact, there are two stated disadvantages: an extra 4 bytes per each non-NULL value slightly longer access time As you pretty much already gathered, the SPARSE option only makes sense for fixed-length datatypes; I can't think of a single reason to use it on variable-length types. I am ...


9

First things first: How much data is there in the table? Number of rows and size of the table? Second: Can you back up and restore this table to a test server and run the alter statement to see the impact (assuming it is not unfeasible due to the table being too large to fit on a non-Production system)? I always find that testing in my environment is more ...


1

From what I've gathered running the alter statement shouldn't take very long as long at the table isn't locked by another process. According to gbn it's just a metadata change: http://stackoverflow.com/questions/7261909/is-it-bad-to-use-alter-table-to-resize-a-varchar-column-to-a-larger-size Also, as for how it's stored, it seems that SQL Server stored the ...


15

No. No gain at all. The manual explicitly states: Tip: There is no performance difference among these three types, apart from increased storage space when using the blank-padded type, and a few extra CPU cycles to check the length when storing into a length-constrained column. While character(n) has performance advantages in some other database ...


1

Think the following are major differences: Nvarchar stores UNICODE data. If you have requirements to store UNICODE or multilingual data, nvarchar is the choice. Varchar stores ASCII data and should be your data type of choice for normal use. Regarding memory usage, nvarchar uses 2 bytes per character, whereas varchar uses 1. JOIN-ing a VARCHAR to ...


3

This behavior for large objects storage can be controlled by the table setting: exec sp_tableoption N'MyTable', 'large value types out of row', <'ON' or 'OFF'> The reference in the SQL Server 2012 documentation is at: http://msdn.microsoft.com/en-us/library/ms173530.aspx Therefore you can control where the space is used, in-row or stored out of row. ...


12

I know that when VARCHAR(MAX)/NVARCHAR(MAX) columns are used the data is stored out of the row... Actually, that depends on the setting of the large value types out of row option, which can be set using sp_tableoption. From the documentation: The default is for MAX values to be stored in-row, up to 8000 bytes, if they fit. Unless you have used ...



Top 50 recent answers are included