Like- SQL Server

Recently I got a query on how to use like clause. As we all know we can use to find word with pattern matching.
Here we will look a sample for it.


CREATE TABLE user_id_list (

user_id VARCHAR(256))

/*If you are using SQL Server 2008*/

INSERT INTO user_id_list

VALUES ('amish'),

('amish123'),

('amish.shah'),

('123456'),

('123amish')

/*If you are using SQL Server 2005*/

INSERT INTO user_id_list

SELECT 'amish'

UNION ALL

SELECT 'amish123'

UNION ALL

SELECT 'amish.shah'

UNION ALL

SELECT '123456'

UNION ALL

SELECT '123amish'

-- To get ID which has only alphabet in it

SELECT user_id

FROM user_id_list

WHERE user_id NOT LIKE '%[^a-z]%'

-- To get ID which has only alphabet and '.' in it

SELECT user_id

FROM user_id_list

WHERE user_id NOT LIKE '%[^a-z,.]%'

-- To get ID which has only alphabet and numbers in it

SELECT user_id

FROM user_id_list

WHERE user_id NOT LIKE '%[^a-z,0-9]%'


Memory properties in Analysis Service

Preallocate
It will allocated specified amount of memory to analysis service.By specifying preallocate Analysis Service uses large memory pages and we can not swap this to page file. It can give better performance to Analysis Service.

So we have to be cautious and also count required amount of memory to other applications before assigning values to it.Its ideal we should keep at least 20% of memory to system.
Service account for analysis service must have lock pages in memory privilege.

TotalMemoryLimit
If value is set to between 0 and 100 its counted as percentage else if its greater than 100 then its count as absolute value in bytes.
It had default value of 80%

LowMemoryLimit
If value is set to between 0 and 100 its counted as percentage else if its greater than 100 then its count as absolute value in bytes.
It has default value of 75%.

if server cross this limits it experience memory pressure. Depending on requirement of memory server feels various levels of pressure.It will start cleaner thread to clean memory depending upon pressure on the server

Levels of Memory Pressure

• When memory is in the specified range of TotalMemoryLimit and LowMemoryLimit there will no pressure

• When memory between LowMemoryLimit and TotalMemoryLimit cleaner will begin to clean memory. It will clean data from cache which is less used compare to highly used cache data

• If memory is greater than TotalMemoryLimit property, cleaner thread will clean the memory upto TotalMemoryLimit. If it can not release memory , it will try to cancel active requests. At this time we will feel various problem with analysis service related to performance.

Except command -SQL Server 2005

Except command can be used when we want rows from table1 which are not in table2.
This command is supported in SQL 2005 and later versions.
Let see an example for this


CREATE TABLE #temp (

id INT,

data VARCHAR(100),

code INT)

INSERT INTO #temp

VALUES (1,'a',1)

INSERT INTO #temp

VALUES (1,'b',2)

INSERT INTO #temp

VALUES (2,'a',1)

INSERT INTO #temp

VALUES (2,'b',2)

CREATE TABLE #temp1 (

id INT,

data VARCHAR(100),

code INT)

INSERT INTO #temp1

VALUES (1,'a',1)

INSERT INTO #temp1

VALUES (1,'b',2)

INSERT INTO #temp1

VALUES (3,'a',1)

INSERT INTO #temp1

VALUES (3,'b',2)


Now we want rows from #temp which are not in #temp1

In SQL 2000 we can do this by matching all columns


SELECT *

FROM #temp t

WHERE NOT EXISTS (SELECT *

FROM #temp1 t1

WHERE t.id = t1.id

AND t.data = t1.data

AND t.code = t1.code)


In SQL 2005 we can do it easily by except


SELECT *

FROM #temp

EXCEPT

SELECT *

FROM #temp1


Interesect Command(SQL Server 2005)

One of new feature of SQL Server 2005 is except and intersect commands.
Lets look how it can be useful.


CREATE TABLE #temp (

id INT,

data VARCHAR(100))



INSERT INTO #temp

VALUES (1,'a')

INSERT INTO #temp

VALUES (1,'b')

INSERT INTO #temp

VALUES (2,'a')

INSERT INTO #temp

VALUES (2,'b')

CREATE TABLE #temp1 (

id INT,

data VARCHAR(100))

INSERT INTO #temp1

VALUES (1,'a')

INSERT INTO #temp1

VALUES (1,'b')

INSERT INTO #temp1

VALUES (3,'a')

INSERT INTO #temp1

VALUES (3,'b')


Now we want matching row from #temp and #temp1

In SQL 2000 we can do this by this way


SELECT *

FROM #temp t

WHERE EXISTS (SELECT *

FROM #temp1 t1

WHERE t.id = t1.id

AND t.data = t1.data)


In SQL 2005 we can do it easily by intersect


SELECT *

FROM #temp

INTERSECT

SELECT *

FROM #temp1


How to get log file size

Recently one guy asked me how to get logfile size and free space in the log file.
We can get this information from dbcc sqlperf command.

Here is code for this


CREATE TABLE #logsize (

database_name VARCHAR(255),

log_size_mb DECIMAL(20,17),

log_space_used_mb DECIMAL(20,17),

status INT)

INSERT INTO #logsize

EXEC( 'DBCC sqlperf ( logspace )')

SELECT *

FROM #logsize

ORDER BY database_name


Cheers