Alex_Kuznetsov - All Comments

re: Using a Foreign key constraint to check validity of History Windows (Start - End Date Windows)

Razvan Socol — Thu, 18 Dec 2008 14:52:05 GMT

We also need a constraint to check that StartedAt<=FinishedAt.

Remove All Non-Numeric Characters From A String

Strate SQL — Thu, 18 Sep 2008 18:54:12 GMT

I completely lack creativity today but thought this link on removing non-numeric characters was kind

re: Remove all non-numeric characters from a string.

Alex_Kuznetsov — Tue, 26 Feb 2008 16:09:33 GMT

I think Simon's solution is better:

http://sqlblogcasts.com/blogs/simons/archive/2008/02/26/String-cleaning-with-TSQL.aspx

re: Remove all non-numeric characters from a string.

Madhivanan — Mon, 25 Feb 2008 08:14:48 GMT

Other similar method

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/18/extract-only-numbers-from-a-string.aspx

re: Vote for TOP(@n) OVER(PARTITION BY ... ORDER BY ...) syntax

Alex_Kuznetsov — Sat, 23 Feb 2008 21:30:40 GMT

Adam,

A duplicate indeed. Note that because all the details of Itzik's suggestion were in an attached document, the search did not bring it up for me.

re: Vote for TOP(@n) OVER(PARTITION BY ... ORDER BY ...) syntax

AdamMachanic — Sat, 23 Feb 2008 20:38:22 GMT

This is a duplicate of the following item:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254390

re: Vote for TOP(@n) OVER(PARTITION BY ... ORDER BY ...) syntax

jamiet — Sat, 23 Feb 2008 18:55:47 GMT

Great idea Alex. I voted 4 and added it to my watch list!!

-Jamie

re: Using a Foreign key constraint to check validity of History Windows (Start - End Date Windows)

AdamMachanic — Thu, 07 Feb 2008 22:20:28 GMT

Clever! I like it!

re: List of tables without primary keys

Uri Dimant — Thu, 07 Feb 2008 05:46:29 GMT

http://dimantdatabasesolutions.blogspot.com/2007/11/tables-that-do-not-have-primary-key.html

re: When It Is OK to Use SELECT * in Production Code.

Madhivanan — Thu, 13 Dec 2007 08:50:41 GMT

Other case to use SELECT * is when checking for existance of the data

IF EXISTS(SELECT * FROM.........)

BEGIN

-- do some stuff here

END

re: When It Is OK to Use SELECT * in Production Code.

Madhivanan — Thu, 13 Dec 2007 08:49:20 GMT

Other case to use SELECT * is when checking for existance of the data

IF EXISTS(SELECT * FROM.........)

BEGIN

-- do some stuff here

END

re: When SUM of Six Floats Depends on Order of Adding

Hugo Kornelis — Fri, 26 Oct 2007 21:45:08 GMT

Hi Alex,

ANY datatype that has rounding issues, can have different results if the order of calculation is changed. That is clearly visible for float, as opposed to decimal, when doing additions with numbers with a finite number of decimal places. But when doing multiplications, the rounding issues of decimal are much easier to spot than the rounding problems of float:

DECLARE @f1 float, @f2 float, @f3 float;

DECLARE @n1 numeric(38,10), @n2 numeric(38,10), @n3 numeric(38,10);

SET @f1 = 123456789.012345;

SET @n1 = 123456789.012345;

SET @f2 = 0.00001;

SET @n2 = 0.00001;

SET @f3 = 10000.0;

SET @n3 = 10000.0;

SELECT (@f1 * @f2) * @f3, (@f1 * @f3) * @f2;

SELECT (@n1 * @n2) * @n3, (@n1 * @n3) * @n2;

Pythian Group Blog » Blog Archive » Log Buffer #68: a Carnival of the Vanities for DBAs

Fri, 26 Oct 2007 18:13:34 GMT

PingBack from http://www.pythian.com/blogs/645/log-buffer-68-a-carnival-of-the-vanities-for-dbas

re: Yet another index covering tip

Alex_Kuznetsov — Sun, 23 Sep 2007 19:24:48 GMT

Hi Simon,

Actually I did not mean INCLUDE clause. I meant 2000 as well as 2005 and I don't think I'm suggesting an overkill. Suppose my Customers table is currently clustered on CustomerID. Suppose I have a NCI on (SignUpDate). Supoose I have a query

SELECT <list of columns from Orders table only>

FROM Customers c JOIN Orders o ON c.CustomerID = o.CustomerID

WHERE o.SignUpDate BETWEEN '20070101' AND '20070201'

At that time my index on (SignUpDate) already covers the query. Note that another index on (SignUpDate, CustomerID) would be essentially the same, of the same size.

However, suppose some time later I want to change my clustering index from CustomerID to (PhoneNUmber, DOB). At that time my index on (SignUpDate) will no longer be coveriny for the query I provided above. However, the index on (SignUpDate, CustomerID) will still be covering.

My point is quite simple: if you explicitly specify columns which you need for index covering, including bookmark columns, you index will be essentially the same, but it will allow you to switch to another CI faster.

re: Yet another index covering tip

simonsabin — Wed, 19 Sep 2007 09:49:45 GMT

I assume you mean use the INCLUDE clause and not just include the column in the keys of the index as that is over kill.

Obviously this isn't possible with SQL2000 and below in which case I would say, droppping and recreating a clustered index should be assessed for impact as it will have a huge impact on your query performance and should only be done whilst assessing ALL queries that access the system.