Tagged Questions

Normally refers to a variable length string data type.

learn more… | top users | synonyms

0
votes
2answers
28 views

VARCHAR primary key - MySQL

Currently, I have a categories table with 2 columns - category VARCHAR(50) NOT NULL PRIMARY KEY and parent VARCHAR(50). The parent column is a foreign key (FK) to the category column. This seems like ...
0
votes
1answer
21 views

Filtering the results of a SELECT query to avoid certain words in the value of a column?

I have a table called videos with a column named title and there is a set of words (around 50 words as of now but the number of words may increase) which is the knowledge base for filtering the ...
5
votes
1answer
96 views

.WRITE clause performance optimization

I have found that you can do high performance updates on varchar(max),nvarchar(max),varbinary(max) columns using the .WRITE clause(example here and here). Unfortunately, there is nothing mentioned ...
1
vote
1answer
24 views

Shrinking VARCHAR with MODIFY: data alignment and potential for data loss

I'm using MODIFY to change (shrink) a number of VARCHAR fields of various lengths in various tables. In each case, the field is called ip and is a textual representation of an IPv4 or IPv6 address. To ...
2
votes
1answer
99 views

varchar storage and comparisons in SQL Server 2008

I have a large table with a structure similar to this: Id bigint primary key Sequence int Parent int foreign key Data1 varchar(20) Data2 varchar(20) Data3 varchar(20) Data4 ...
1
vote
1answer
179 views

What does it mean? ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Q 1: Whenever an Oracle SP wants to return error code/message, it fails returning below exeption: OLE DB provider "OraOLEDB.Oracle" for linked server "ORASRV" returned message "ORA-06502: PL/SQL: ...
0
votes
0answers
29 views

Performance implications of mixing VARCHAR and CHAR fields in MySQL Table

I read the following answers in this forum: What is the performance impact of using CHAR vs VARCHAR on a fixed-size field? MySQL: Does the order of the fields in a table make a difference in ...
0
votes
1answer
96 views

Get complete value of varchar(max) field - SQL Server 2005

I have searched for a couple of hours but cannot find the exact solution for this. In my SQL Server 2005 database table (tableA) there is a varchar(max) column. I want to get complete value of that ...
0
votes
1answer
35 views

Varchar indexing

I am very rusty with MySQL and I'd like some advice. I have several software tools that are deployed throughout the company and I am writing a DLL to keep track of the usage, errors and stuff of some ...
0
votes
1answer
67 views

MySQL 'copying to tmp table' while enlarging VARCHAR

One user called today because he has tried to enlarge a VARCHAR column (from 20 to 100) in a slightly large table resulting in a write-locked table. I watched PROCESSLIST and saw that ALTER TABLE ...
2
votes
1answer
6k views

Converting a VARCHAR to VARBINARY

I've been keeping a log of expensive running queries, along with their query plans, in a table to allow us to monitor trends in performance and identify areas that need optimising. However, it's come ...
2
votes
1answer
178 views

Getting Visual Studio to deploy a UDF to Return NVARCHAR(MAX)

I have a SQLCLR stored procedure with the following signature in Visual Studio 2013: [SqlFunction(DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read)] public static ...
1
vote
1answer
220 views

Remove single quotes from db2 column name

I have following query which i am trying to execute against db2 database server SELECT REPLACE('\'abc\'','\'','') FROM TN_DAY_PROCESS I am expecting the output like abc without single quotes from ...
0
votes
1answer
79 views

Why is this query slow, it seems simple

I have a very typical table of user information for my application, with an average set of columns (ints, varchars, etc). In this table are two varchar(100) fields: emailAddress and emailAddress2. ...
1
vote
1answer
135 views

What is a storage need for MySQL index on Varchar(100) in InnoDB?

I have a InnoDB table, which has EMail column (VARCHAR(100)), now I will run script which will hash some of that email addresses. I am wondering, if there is a difference in data storage (for example ...
2
votes
2answers
203 views

Should I replace my varchar primary key with an integer primary key?

I have a table that has a varchar primary key that's associated with "Base Product Codes". They rarely change, and if they do, it's usually just being deleted or added. I figured it would be better ...
1
vote
2answers
168 views

Indexing varchar column to search for first letter

I'm trying to optimize a legacy web-app where users can ask for a list of all records whose name starts with a given character. The query to optimize is: SELECT oggetto.id_oggetto, titolo, anno, ...
1
vote
1answer
240 views

char vs varchar in mysql

I was reading the book MySQL high performance, it has mentioned that : CHAR is also better than VARCHAR for data that’s changed frequently, because a fixed-length row is not prone to ...
0
votes
1answer
104 views

Implicitly (or proxy) convert to Nvarchar on incoming queries from legacy or 3rd party applications

We have a 3rd party application of which we don't have the source code. This application executes queries on our database without using the "N" prefix before literal strings. It does this in inserts ...
0
votes
1answer
153 views

Do certain string patterns affect query performance on a VARCHAR column?

I am a beginner on MySQL Administration and performance problems. Now this is confusing me. I have a VARCHAR column identity, the string in it is consisted of a Class type and an id, like note:123, ...
1
vote
1answer
1k views

Varchar index - will hashing value make it faster?

I have a VARCHAR(1000) column in a table. It will contain strings that will not be guaranteed to be unique. I have a query that searches this column as part of a WHERE IN clause, the list of values in ...
0
votes
2answers
639 views

VARCHAR, InnoDB MySQL, is inline allocated space variable or fixed?

For a log table, I have a custom message column. It will never be updated. It will be searched with LIKE conditions. It will be capped at 1KB. TEXT fields are slower than VARCHAR fields for ...
1
vote
1answer
112 views

Changing TEXT to VARCHAR

In a MySQL database (InnoDB) we have a table containing roughly 60.000.000 rows. One of the columns is of type text. Currently the longest entry has length 360 (found using SELECT MAX(LENGTH(value)) ...
5
votes
3answers
3k views

Write differences between varchar and nvarchar

Currently in our SQL Server 2012 database, we're using varchar, and we'd like to change that nvarchar. I've generated a script to do that. My question is are there any differences in how SQL Server ...
10
votes
1answer
6k views

Possible INDEX on a VARCHAR field in MySql

I am working in a MySql database, with a table like this: +--------------+ | table_name | +--------------+ | myField | +--------------+ ...and I need to make a lot of queries like this (with ...
5
votes
1answer
157 views

Unexpected Unique Constraint Violation

Given the following CREATE TABLE statement: CREATE TABLE [dbo].[Unit] ( [id] SMALLINT NOT NULL IDENTITY(1,1), [name] VARCHAR(100) NOT NULL, CONSTRAINT ...
3
votes
1answer
517 views

Will one nvarchar column affect query performance?

I have one column that I recently converted to nvarchar(100) to store Asian characters. The other columns in this table, and the other tables in the same database, use varchar, int, bit and datetime. ...
7
votes
7answers
2k views

MySQL Indexing VarChar

I am trying to index my blogentries database for better performance but found an issue. Here is the structure: CREATE TABLE IF NOT EXISTS `blogentries` ( `id_id` int(11) NOT NULL AUTO_INCREMENT, ...
5
votes
3answers
460 views

MySQL varchar(255) limitation & Anomaly

I have defined a column in my MySQL database as : ALTER TABLE `my_table` CHANGE `desc` `desc` VARCHAR( 255 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL However, when I enter text ...
1
vote
1answer
691 views

How to change the display of an empty string in mysql command line tool?

I'm maintaining a database with InnoDB tables. These tables have some columns of type (from show create table): `val0` varchar(30) default NULL, `val1` varchar(30) default NULL, etc... From the ...
3
votes
2answers
8k views

Efficient way of changing VARCHAR to NVARCHAR fields in a large table in SQL Server 2008?

I am aware of when adding new fields to large tables, it is recommended to add them to the end of the fields rather than somewhere in the middle, and wondering if something like this applies when ...
4
votes
3answers
3k views

Should I add an arbitrary length limit to VARCHAR columns?

According to PostgreSQL's docs, there's no performance difference between VARCHAR, VARCHAR(n) and TEXT. Should I add an arbitrary length limit to a name or address column?
11
votes
1answer
12k views

Varchar(Max) field cutting off data after 8000 characters SQL Server 2008

I have a field to store some data, the field is declared as varchar(Max). To my understanding this should be storing 2^31 - 1 characters but when I enter some content over 8000 chars it cuts the rest ...
4
votes
2answers
588 views

varchar performance impact

This question has been asked before ( speed impact of using varchar in MSSQL 2008 ), but the answers are not detailed enough to my satisfaction. Admitedly this is not a current issue and mainly for ...
9
votes
1answer
10k views

SQL Server 2008 Change length of varchar on live prod table

I have a MS SQL Server 2008 R2 DB server currently in use with a production app. A new enhancement to the app now requires a varchar(100) column in a table to be increased in length. Can the ...
2
votes
2answers
106 views

Storing NULL Values in large VARCHAR fields

I have a table that represents a "note". This note has things like a title and various other meta data. However, I'm trying to figure out the best practice for storing the contents of the note. ...
2
votes
1answer
1k views

Oracle SQL count occurances of character in VARCHAR

Is is possible to count the number of occurrences of a character in a VARCHAR in oracle? Something that would allow me to determine '11001110' has 5 ones?
3
votes
1answer
330 views

MySQL text field without quote

I've just run into something strange. The base_id is a varchar(255). When I do a SELECT without quote, it scans the whole table: mysql> EXPLAIN SELECT sid FROM rf_fo.scald_atoms WHERE base_id = ...
8
votes
2answers
3k views

SQL Server VARCHAR Column Width

Searching around the web, I have found conflicting advice on whether there is a performance impact when specifying overly-wide VARCHAR columns, e.g. VARCHAR(255) when VARCHAR(30) will probably do. I ...
3
votes
1answer
3k views

Rows with a variable number of columns?

I'm trying to design a database for a program that is storing and manipulating tweets off of Twitter. I'm using code to fragment a tweet into words, usernames, and hashtags, so I'm meeting ...
1
vote
1answer
135 views

String Pooling for MySQL

So I'm having this table that logs all errors happened in our production code. It looks like this: id | date | type | message | file | line ...
3
votes
3answers
5k views

Does an Oracle nvarchar2 take twice as much space as varchar2?

Regarding point 4 of this question (which is in reference to MS SQL Server): VARCHAR does not store Unicode characters. NVARCHAR does store Unicode characters. Today's applications should ...
4
votes
3answers
637 views

Why is Row Overflow not working on SQL Server 2005

I have a SQL Server 2005 database with a table that has around 860 columns. Aside from the ID column all other columns are varchar(N) where N is either 100 or 500. Upon inserting a new row (via an ...
12
votes
3answers
1k views

Is there any point in undersizing VARCHAR columns?

Googling around there seems to be mixed reports whether the size of a VARCHAR2 column in Oracle impacts performance or not. I would like to give the question of VARCHAR size a little twist and hope ...
17
votes
4answers
2k views

What are the consequences of setting varchar(8000)?

Since varchar takes disk space proportional to the size of the field, is there any reason why we shouldn't always define varchar as the maximum, e.g. varchar(8000) on SQL Server? On create table, if ...
16
votes
3answers
20k views

What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?

I have an indexed column that stores an MD5 hash. Thus, the column will always store a 32-character value. For whatever reason, this was created as a varchar rather than a char. Is it worth the ...
4
votes
4answers
456 views

How much will using a varchar(15) PK affect my tables performance?

I am parsing log file lines from a legacy, proprietary application into a nice easy to query DB. The log lines have no unique integer ID. They do have a UNIX timestamp as an 8 character hex string. ...
5
votes
3answers
2k views

Which problems arise, declaring the size of all varchar parameters as max in stored proc?

Looking at this question, it seems, that other sites too have the problem of increasing column sizes in the process of time. Increasing just the size of a column in a table is a rather simple task. ...
16
votes
5answers
7k views

Performance implications of MySQL VARCHAR sizes

Is there a performance difference in MySQL between varchar sizes? For example, varchar(25) and varchar(64000). If not, is there a reason not to declare all varchars with the max size just to ensure ...