SQL SERVER – Do Not Store Images in Database – Store Location of Images (URL)

Just a day ago I received phone call from my friend in Bangalore. He asked me

What do I think of storing images in database and what kind of datatype he should use?

I have very strong opinion about this issue.

I suggest to store the location of the images in the database using VARCHAR datatype instead of any BLOB or other binary datatype.

Storing the database location reduces the size of database greatly as well updating or replacing the image are much simpler as it is just an file operation instead of massive update/insert/delete in database.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

SQL SERVER – UDF – Remove Duplicate Chars From String

Few days ago, I received following wonderful UDF from one of this blog reader. This UDF is written for specific purpose of removing duplicate chars string from one large string. Virendra Chauhan, author of this UDF is working as DBA in Lutheran Health Network.

CREATE FUNCTION dbo.REMOVE_DUPLICATE_INSTR
(@datalen_tocheck INT,@string VARCHAR(255))
RETURNS VARCHAR(255)
AS
BEGIN
DECLARE
@str VARCHAR(255)
DECLARE @count INT
DECLARE
@start INT
DECLARE
@result VARCHAR(255)
DECLARE @end INT
SET
@start=1
SET @end=@datalen_tocheck
SET @count=@datalen_tocheck
SET @str = @string
WHILE (@count <=255)
BEGIN
IF
(@result IS NULL)
BEGIN
SET
@result=''
END
SET
@result=@result+SUBSTRING(@str,@start,@end)
SET @str=REPLACE(@str,SUBSTRING(@str,@start,@end),'')
SET @count=@count+@datalen_tocheck
END
RETURN
@result
END
GO>, 1)

Usage:
SET CONCAT_NULL_YIELDS_NULL OFF

SELECT dbo.Remove_duplicate_instr(<CHARacter length OF a

duplicate SUBSTRING >,<string contain duplicate>)
Example:
To keep char set in a string unique and remove duplicate 3 char long string run this UDF as inline function.

SET CONCAT_NULL_YIELDS_NULL OFF

SELECT dbo.Remove_duplicate_instr(3,f123456789123456456
Resultset:
123456789

Reference : Pinal Dave (http://blog.SQLAuthority.com), Virendra Chauhan (DBA)

SQL SERVER – 2005 Collation Explanation and Translation – Part 2

Following function return all the available collation of SQL Server 2005. My previous article about the SQL SERVER – 2005 Collation Explanation and Translation.

SELECT *
FROM sys.fn_HelpCollations()

Result Set: (only few of 1011 records)
Name Description
Latin1_General_BIN Latin1-General, binary sort
Latin1_General_BIN2 Latin1-General, binary code point comparison sort
Latin1_General_CI_AI Latin1-General, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive
Latin1_General_CI_AI_WS Latin1-General, case-insensitive, accent-insensitive, kanatype-insensitive, width-sensitive
Latin1_General_CI_AI_KS Latin1-General, case-insensitive, accent-insensitive, kanatype-sensitive, width-insensitive
Latin1_General_CI_AI_KS_WS Latin1-General, case-insensitive, accent-insensitive, kanatype-sensitive, width-sensitive
Latin1_General_CI_AS Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive
Latin1_General_CI_AS_WS Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-sensitive
Latin1_General_CI_AS_KS Latin1-General, case-insensitive, accent-sensitive, kanatype-sensitive, width-insensitive
Latin1_General_CI_AS_KS_WS Latin1-General, case-insensitive, accent-sensitive, kanatype-sensitive, width-sensitive
Latin1_General_CS_AI Latin1-General, case-sensitive, accent-insensitive, kanatype-insensitive, width-insensitive
Latin1_General_CS_AI_WS Latin1-General, case-sensitive, accent-insensitive, kanatype-insensitive, width-sensitive
Latin1_General_CS_AI_KS Latin1-General, case-sensitive, accent-insensitive, kanatype-sensitive, width-insensitive
Latin1_General_CS_AI_KS_WS Latin1-General, case-sensitive, accent-insensitive, kanatype-sensitive, width-sensitive
Latin1_General_CS_AS Latin1-General, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive
Latin1_General_CS_AS_WS Latin1-General, case-sensitive, accent-sensitive, kanatype-insensitive, width-sensitive
Latin1_General_CS_AS_KS Latin1-General, case-sensitive, accent-sensitive, kanatype-sensitive, width-insensitive
Latin1_General_CS_AS_KS_WS Latin1-General, case-sensitive, accent-sensitive, kanatype-sensitive, width-sensitive

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Fix : Error : Server: Msg 131, Level 15, State 3, Line 1 The size () given to the type ‘varchar’ exceeds the maximum allowed for any data type (8000)

Error:
Server: Msg 131, Level 15, State 3, Line 1 The size () given to the type ‘varchar’ exceeds the maximum allowed for any data type (8000)

When the the length is specified in declaring a VARCHAR variable or column, the maximum length allowed is still 8000.

Fix/WorkAround/Solution: Use either VARCHAR(8000) or VARCHAR(MAX) . VARCHAR(MAX) of SQL Server 2005 is replacement of TEXT of SQL Server 2000.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Repeate String N Times Using String Function REPLICATE

I came across this SQL String Function few days ago while searching for Database Replication. This is T-SQL Function and it repeats the string/character expression N number of times specified in the function.

SELECT REPLICATE( ' http://www.SQLAuthority.com ' , 9 )

This repeats the string http://www.SQLAuthority.com to 9 times in result window. I think it is fun utility to generate repeated text if ever required.

Result Set:
http://www.SQLAuthority.com http://www.SQLAuthority.com http://www.SQLAuthority.com http://www.SQLAuthority.com http://www.SQLAuthority.com http://www.SQLAuthority.com http://www.SQLAuthority.com http://www.SQLAuthority.com http://www.SQLAuthority.com

(1 row(s) affected)

Reference : Pinal Dave (http://blog.SQLAuthority.com) , BOL

SQL SERVER – 2005 Row Overflow Data Explanation

In SQL Server 2000 and SQL Server 2005 a table can have a maximum of 8060 bytes per row. One of my fellow DBA said that he believed that SQL Server 2000 had that restriction but SQL Server 2005 does not have that restriction and it can have a row of 2GB. I totally agreed with him but after we discussed this problem in depth, we realized that there are more into it than only 8060 bytes limit.

It is still true for SQL Server 2005 that a table can have maximum of 8060 bytes per row however the restriction has exclusions of few data types. CLR User Defined data-types as well as varchar, nvarchar, varbinary, sql_variants are not limited to bytes per row limitation. However, those data types have limitation that they can not be more than 8000 bytes individually. In SQL Server 2005 one table row can contain more than one varchar(8000) fields. One more thing, the exclusions have exclusions also the limit of each individual column max width of 8000 bytes does not apply to varchar(max), nvarchar(max), varbinary(max), text, image or xml data type columns. Though, sum of all other other kind of data type should be less than 8060 bytes. That means one table can not have three columns with char(4000) or 100K bit field columns.

In summary in SQL Server 2005

  • Table row can have more than 8060 bytes. (2GB Max)
  • varchar, nvarchar, varbinary, sql_variant, or CLR user-defined type columns can have max 8000 bytes.
  • varchar(max), nvarchar(max), varbinary(max), text, image or xml data type columns have no restrictions.
  • All the other data type columns (other than mentioned in above three points) width addition must be still under 8060 byte row limit.
  • Index can only be created which falls with-in 8060 byte row limit.

Reference : Pinal Dave (http://blog.SQLAuthority.com) , BOL, SQL Stuff

SQL SERVER – 2005 – UDF – User Defined Function to Strip HTML – Parse HTML – No Regular Expression

One of the developer at my company asked is it possible to parse HTML and retrieve only TEXT from it without using regular expression. He wanted to remove everything between < and > and keep only Text. I found question very interesting and quickly wrote UDF which does not use regular expression.

Following UDF takes input as HTML and returns TEXT only. If there is any single quotes in HTML they should be replaced with two single quotes (not double quote) before it is passed as input to function.

CREATE FUNCTION [dbo].[udf_StripHTML]
(@HTMLText VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE
@Start INT
DECLARE
@End INT
DECLARE
@Length INT
SET
@Start = CHARINDEX('<',@HTMLText)
SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))
SET @Length = (@End - @Start) + 1
WHILE @Start > 0
AND @End > 0
AND @Length > 0
BEGIN
SET
@HTMLText = STUFF(@HTMLText,@Start,@Length,'')
SET @Start = CHARINDEX('<',@HTMLText)
SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))
SET @Length = (@End - @Start) + 1
END
RETURN
LTRIM(RTRIM(@HTMLText))
END
GO

Test above function like this :

SELECT dbo.udf_StripHTML('<b>UDF at SQLAuthority.com </b><br><br><a href="http://www.SQLAuthority.com">SQLAuthority.com</a>')

Result Set:

UDF at SQLAuthority.com SQLAuthority.com

If you want to see this example in action click on Image. It will open large image.

Reference : Pinal Dave (http://blog.SQLAuthority.com)