1

I am using Sql Server 2014 Express and have a varchar(max) column. I some cases I need to use an special character to give special meaning to this field.

I have for example this value: "123.456.234", but I would like to use something like that: "678.*789.123". In this second case the * has the meaning that the value 789 is not a standard value (standard value inside my application logic) and the other two values 678 and 123 are standard.

But I don't know if using "*" I will have problems when I will want to search using this column in the queries. I can use any character, I only want to separate the meaning of a particular value in the string.

Thanks so much.

Álvaro García
  • 18,114
  • 30
  • 102
  • 193
  • You should put the meaning in another column. It will be a lot easier for the server and it will have the possibility to use indexes. – Julien Vavasseur Oct 29 '15 at 10:21
  • 2
    Check SQL Server [wildcard](https://msdn.microsoft.com/en-us/library/ms179859.aspx) . – qxg Oct 29 '15 at 10:21
  • 1
    @JulienVavasseur You cannot index a varchar(max) – paparazzo Oct 29 '15 at 10:24
  • @Frisbee I meant index on the new column with a meaning which should not be varchar(max) but rather int or something small and easy to query. This looks like a design problem and will lead to performance issues when searching for wildcard varchar (=> table scan) – Julien Vavasseur Oct 29 '15 at 10:26

1 Answers1

1

Search speed of one character to another is the same.

If you are using like then if you use one of the wildcard characters you would have to escape it. But * is not a wildcard character.

If those are three different values then they should not be in the same entry. Look up third normal form (3NF).

Varchar(max) cannot be indexed.

paparazzo
  • 44,497
  • 23
  • 105
  • 176
  • The text is the path for a node in a tree, so each node has the full path to this nodes. However, some nodes, its position can have a different consideration, and this affect to the child nodes. So a way to avoid to have to search for all ancestors to check if someone is an special node, I was thinking yo use this caracter in the path, so I can check in the node if it contains this character, then has an special ancestor. – Álvaro García Oct 29 '15 at 10:39
  • 1
    OK then back to search speed of one character to another is the same – paparazzo Oct 29 '15 at 11:09
  • I agree but multiple values in a single intersection violates 1NF not 3NF. :) – Sean Lange Oct 29 '15 at 13:32
  • @SeanLange 1NF is part of 3NF. – paparazzo Oct 29 '15 at 14:23
  • @Frisbee true but if they go looking at 3NF they won't find any information about multiple values because that is not part of 3NF. It can only be 3NF if all aspects of 2NF are met. And it can't be 2NF unless all aspects of 1NF are met. I was just trying to point out that the particular aspect is part of 1NF. – Sean Lange Oct 29 '15 at 14:40
  • @SeanLange OK understand – paparazzo Oct 29 '15 at 14:45