Archive

Archive for the ‘ASCII’ Category

What are SQL Query Hints?

July 5, 2014 Leave a comment

SQL server usually selects the best query plan required to execute a T-SQL query. However, there might be cases where the plans selected are not the best. In such situations, you can use the query hints to suggest the best possible plan/method of executing a query. The Query hints can be applied on all operators in a query. These hints apply to SELECT, DELETE, INSERT, UPDATE and MERGE. You can use the Query hints in the OPTION clause after the query.

Few commonly used Query hints with examples are as below.

Read more…

 

Compound Operators in SQL Server

July 5, 2011 Leave a comment

There was a time when we all wished SQL Server supports compound operators just like any other programming languages. Wouldn’t it be nice to have a shorter syntax for assigning the result of an arithmetic operator?  Well, SQL server 2008 introduced Compound operators that allow you to perform several arithmetic operations using an operand along with equal to (=) operand.

 For example:

Declare @var1  int

Set @var1 = 150

Set @var1 += 100

Read more…

 

 

GUID (Globally Unique Identifier) in SQL Server

October 7, 2010 1 comment

The GUID (uniqueidentifier) data type is one of the most interesting data types available. uniqueidentifier column holds a GUI, a string of 32 random hexa-decimal characters (0-9, A-F) in blocks separated by hyphens.  A GUID will look like

5166AA1D-A18F-4D3C-A7CA-3F3CBF8CEE2B 

EDCF4B8A-4154-449B-972A-2224F8450BF1

CEA4C383-51FD-4B99-A67D-F5C34D5F1013     

5D9FDC84-632D-4450-B183-04CBBEE87DC6

GUIDs have both Pros and cons. GUIDs are pretty big in size and require 16 bytes of storage. This usually makes it slow during joins of large dataset.

Continue Reading …

 

Save storage disk space by replacing “space” with “horizontal tabs”

August 19, 2009 Leave a comment

This was a simple experiment that I performed to find, how much difference it makes by replacing some of the characters in a field with others. The basic requirement was to have a varchar / nvarchar field with huge data :). I created a sample table with a field of datatype  nvarchar(max). The table used no disk space

name                  rows    reserved    data    index_size    unused
TESTTABLE    0              0 KB          0 KB     0 KB               0 KB

Then inserted data into this field. Also didn’t forget to add lot of  “space” in the data. I inserted around 17 records in the table and then ran the stored proc to find the space used by the table.

the result was

name                 rows    reserved     data            index_size    unused
TESTTABLE     17         984 KB       968 KB 8 KB                  8 KB

I replaced the 3 consecutive spaces with a “horizontal tab” by using the query

UPDATE TESTTABLE
SET CHARS = REPLACE(CHARS,’   ‘,CHAR(9))

then i checked the space used by the table

name                 rows          reserved    data           index_size    unused
TESTTABLE      17             968 KB        696 KB 8 KB                 264 KB

you might have already noticed that, there is a huge differnce in the data size. The size was reduced to 696KB from 968KB. This was for a small table with only 17 records, imagine for a table with millions of records :) ,  it can make a huge difference. Theoratically speaking 3 charchters were replace with a single character with out affecting the way the data is displayed in my UI . This saved me 4 bytes for each replace ( 1 characters = 2 byte for nvarchar, therefore 3-1 = 2 characters removed for each replace ). This does not mean that you can replace all characters with someting else.

One of my record had  23345 characters, the replace reduced it to  14761 characters

The unused space can be regained by using some techniques like shrinking the database.

Now the question in your mind might be, does the same work for char / nchar datatypes.  I leave that to you to find out. May be,  you already have the answer :).

Please use the content at your own risk. In no event shall the
authors be liable for any claim, damages/issues that may be caused
by using the content.

Insert “Space”,”new line”,”tab” characters into a field as value

August 10, 2009 11 comments

There are times when you need to insert some special characters such as “newline”,”tab” etc as values into a field. Usually every one tries with ‘\n’ or ‘\t’, in vain. we can do this by using the ASCII value of the character. The steps are very simple. if you want to insert a special character find the ASCII value of that character like ‘ 9 for tab’, ‘ 12 for new line’ etc. you can find the lists of characters with its ASCII values at https://awesomesql.wordpress.com/2009/08/10/ascii-character-set-table. Once this is done, concatenate your string with the character of that particular ASCII value as

‘my string and’+ char(12)+’someting’

this will be

my string and

something

Read More . . . . 

 

 

ASCII character set table

August 10, 2009 12 comments

ASCII character set and values in decimal,hexadecimal,oct

Special Characters

Char Oct Dec Hex Remarks
NUL 0 0 0 Null character
SOH 1 1 1 Start of heading
STX 2 2 2 Start of text
ETX 3 3 3 End of text
EOT 4 4 4 End of transmission
ENQ 5 5 5 Enquiry, goes with ACK
ACK 6 6 6 Acknowledge, clears ENQ logon hand
BEL 7 7 7 Bell
BS 10 8 8 Backspace
HT 11 9 9 Horizontal tab
LF 12 10 a Line Feed
VT 13 11 b Vertical tab
FF 14 12 c Form Feed, page eject
CR 15 13 d Carriage Return
SO 16 14 e Shift Out
SI 17 15 f Shift In
DLE 20 16 10 Data link escape
DC1 21 17 11 XON, with XOFF to pause listings
DC2 22 18 12 Device control 2, block-mode flow control
DC3 23 19 13 XOFF, with XON is TERM=18
DC4 24 20 14 Device control 4
NAK 25 21 15 Negative acknowledge
SYN 26 22 16 Synchronous idle
ETB 27 23 17 End transmission block
CAN 30 24 17 Cancel line
EM 31 25 19 End of medium
SUB 32 26 1a Substitute
ESC 33 27 1b Escape
FS 34 28 1c File separator
GS 35 29 1d Group separator
RS 36 30 1e Record separator
US 37 31 1f Unit separator

Prinatable characters

Read complete list here . . . . 

Categories: ASCII, SQL Server