Hot answers tagged

73

SUGGESTION #1 : Standard Indexing CREATE TABLE mytable ( id int not null auto_increment, myfield varchar(255) not null, primary key (id), key (myfield) ); If you index like this, you can either look for the whole string or do left-oriented LIKE searches SUGGESTION #2 : FULLTEXT Indexing CREATE TABLE mytable ( id int not null ...


51

You need to be sure that you prefix Unicode string literals with an N prefix. For example these will work differently if the underlying data type is NVARCHAR: CREATE TABLE dbo.t(c NVARCHAR(32)); INSERT dbo.t(c) SELECT 'រៀន'; INSERT dbo.t(c) SELECT 'នរៀ'; INSERT dbo.t(c) SELECT N'រៀន'; SELECT c FROM dbo.t; SELECT c FROM dbo.t WHERE c = 'រៀន'; SELECT c ...


45

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 ...


41

The biggest concern is that nvarchar uses 2 bytes per character, whereas varchar uses 1. Thus, nvarchar(4000) uses the same amount of storage space as varchar(8000)*. In addition to all of your character data needing twice as much storage space, this also means: You may have to use shorter nvarchar columns to keep rows within the 8060 byte row limit/8000 ...


39

Size each and every column appropriately. Do NOT use a "standard" size for each column. If you only need 30 characters, why create a column that can handle 255? I'm so glad you're not advocating using varchar(max) for your string columns. This is especially prudent advice if you ever need to index a column, or if you are using a column as a primary key ...


38

The maximum size of limited character types (e.g. varchar(n)) in Postgres is 10485760. You can check this in that way: create table test(id serial primary key, str varchar(10485761)); ERROR: length for type varchar cannot exceed 10485760 The limit is defined in the following fragment of source code (htup_details.h), however is not explicitly mentioned in ...


33

Datetime is not precise to the level of 1 millisecond. What you are asking for is not possible unless you change to a different datatype (i.e. datetime2). Documentation Important quote: Accuracy Rounded to increments of .000, .003, or .007 seconds


32

Should I always use (n)varchar(max) for text columns? No. For SQL Server, the max data types should only be specified when there is no alternative. One should instead choose the correct base type (varchar or nvarchar) and specify an explicit maximum length that is appropriate to the data to be stored. Physical storage is identical whether the column is ...


31

You need to use a specific style when you expect to keep the same binary value when converting from a string. Otherwise SQL Server tries to encode the string the same way it would encode 'bob' or 'frank'. That said, your input string doesn't look correct - there is either a byte missing or one byte too many. This works fine if I drop the trailing E: SELECT ...


30

I can see why you're misunderstanding this - it's a little tricky. These are all valid: VARCHAR(1) - one character string VARCHAR(4000) - 4,000 characters VARCHAR(8000) - 8,000 characters - and if you use a number for this field's definition, that's the highest NUMBER you can use, but watch this: VARCHAR(MAX) - that one holds up to 2GB. And yes, if you try ...


27

CHAR and VARCHAR are implemented exactly the same in Postgres (and Oracle). There is no difference in speed when using those data types. However, there is one difference that can make a difference in performance: a char column is always padded to the defined length. So if you define a column as char(100) and one as varchar(100) but only store 10 characters ...


20

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 ...


20

For the legacy CE, I see the estimate is for 3.16228 % of the rows – and that is a "magic number" heuristic used for column = literal predicates (there are other heuristics based on predicate construction – but the LEN wrapped around the column for the legacy CE results matches this guess-framework). You can see examples of this on a post on ...


20

Unsurprisingly, the manual is right. But there is more to it. For one, size on disk (in any table, even when not actually stored on disk) can be different from size in memory. On disk, the overhead for short varchar values up to 126 bytes is reduced to a 1 byte as stated in the manual. But the overhead in memory is always 4 bytes (once individual values are ...


19

Others have already pointed out that that the number of bytes required to store the length is fixed. I wanted to focus on this part in your question: Does it matter anymore at this point? You have your question tagged with enterprise edition, which generally means you'll have a fair amount of data. Often differences of one byte per row really don't ...


19

Regardless of specific datatype, you need to be able to store whatever the application requests to be stored. You cannot specify something smaller than the max size of what will actually be saved. You also do not need, nor want, to specify a column length larger than the maximum actual size that will be stored for a variety of reasons: query memory ...


18

MySQL enables you to define prefixed index which means you define first N characters from original string to be indexed, and the trick is to choose a number N that’s long enough to give good selectivity, but short enough to save space. The prefix should be long enough to make the index nearly as useful as it would be if you’d indexed the whole column. ...


17

Values that exceed 8000 bytes cannot be stored "inline". They are stored on LOB pages. You can see this with sys.dm_db_index_physical_stats. Start with a simple table: USE tempdb; DROP TABLE IF EXISTS #LOB_FOR_ME; CREATE TABLE #LOB_FOR_ME ( ID BIGINT, MAX_VERNON_WAS_HERE VARCHAR(MAX) ); CREATE INDEX IX ON #LOB_FOR_ME (ID) INCLUDE (MAX_VERNON_WAS_HERE); ...


15

SQL Server uses column lengths when allocating memory for query processing. So, yes, in short, you should always size columns appropriately for the data. Memory allocations are based on the number of rows returned by the query multiplied by half the declared length of the column. Having said that, in this case where you've got 6 rows you probably don't ...


14

With 6 rows, no, there will be no observable benefit. That entire table will fit on a single page so lowering the maximum potential space you’ll use on that page while still occupying that entire page is really no different in all practical sense. On larger tables, though, right-sizing is crucial. The reason is that memory estimates will be based on the ...


13

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 ...


13

Is there an explanation for the cardinality estimate of 1.0003 for SQL 2014 while SQL 2012 estimates 31,622 rows? I think @Zane's answer covers this part pretty well. Is there a good workaround? You could try creating a Non-Persisted Computed Column for LEN(cust_nbr) and (optionally) create a Non-Clustered Index on that Computed Column. That should get ...


13

The DateAdd function is what you are looking for. Use millisecond as the first parameter to the function, to tell it that you are adding milliseconds. Then use 1 as the second parameter, for the number of milliseconds to add. Here is an example, grabbing the current time into a variable, and then adding one millisecond to it and saving the result as a ...


13

@Doug-Deden has the right starting point, but I just wanted to try to answer what I thought was the original intention of the question - how to apply it to a result set with increasing milliseconds per row. In that case, you can use ROW_NUMBER and a Common Table Expression (edit as needed for you table structure, including joins, etc.). Select to show ...


12

Most of the answers in this thread are five eight years old, written before InnoDB and utf8 were defaults. So, let me start over... When a query needs an internal temporary table it tries to use a MEMORY table. But MEMORY cannot be used if TEXT/BLOB columns being fetched, even TINYTEXT. VARCHAR bigger than some amount, probably 512 in the current version....


12

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 ...


12

As suggested by @Josh Kupershmidt and @JoeNahmias the solution is to use UNIQUE on md5 hash of the long value. However PostgreSQL 9.3 doesn't support expressions in UNIQUE constraints so an index, that supports expressions, has to be used: create unique index unique_data_url_index on mytable (md5(data_url));


9

VARCHAR column as Primary Key is not a good choice as normally we create Cluster Index on same column. Cluster Index on VARCHAR columns is a bad choice because of expected high fragmentation rate. Every new inserted key value will try to find its place somewhere between existing keys and normally cause page split and high index fragmentation. As a result ...


8

'abcd' in CHAR(72) CHARACTER SET ascii occupies 72 bytes on disk. 'abcd' in CHAR(72) CHARACTER SET utf8 occupies 3*72 bytes on disk. 'abcd' in CHAR(72) CHARACTER SET utf8mb4 occupies 4*72 bytes on disk. 'abcd' in VARCHAR(72) occupies 1+4 bytes on disk. 'abcd' in TINYTEXT occupies 1+4 bytes on disk. 'abcd' in TEXT occupies 2+4 bytes on disk. 'abcd' in ...


8

This is going to read like a paranoid's answer, but there aren't only storage and performance considerations. The database itself doesn't control its clients, and clients can't be assumed to always securely insert user input - even if a database is designed to be used only with a .net application that uses Entity Framework to encapsulate transactions and ...


Only top voted, non community-wiki answers of a minimum length are eligible