Advertisements

Archive

Posts Tagged ‘MSDN TSQL forum’

What does “on-disk structure” means w.r.t Indexes in SQL Server – MSDN TSQL forum

August 18, 2015 Leave a comment

–> Question:

According to MSDN, an index is an on-disk structure associated with a table or view that speeds retrieval of rows from the table or view.

In this definition, I don’t get what on-disk structure means.
 

–> My Answer:

Do not confuse with on-disk structure statement, not only index, a table is also an on-disk structure as all you data sits on-disk.

Check my response on you other similar question in MSDN TSQL forum.

Just like a table, an index is also on disk which can be built on top a table or separately.

– On top to restructure the whole table which is called the Clustered Index. Clustered Index is nothing but the table itself converted from a heap to a b-tree.

– Separately like a Non-Clustered index, which is a copy of the table with specific columns, created separately for performance reasons to read data will less columns.
 

Ref Link.

Advertisements

How exactly STATIC CURSOR works internally – MSDN TSQL forum

August 12, 2015 Leave a comment

–> Question:

In MSDN help mentions static cursor as follows:

STATIC: Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications

It say’s that modifications is not allowed in the static cursor.

I have a questions regarding that:

declare ll cursor global static
for select name, salary from ag
open ll
fetch from ll

while @@FETCH_STATUS=0
fetch from ll
update ag set salary=200 where 1=1

close ll
deallocate ll

In “AG” table, “SALARY” was 100 for all the entries.

When I run the Cursor, it showed the salary value as “100” correctly.

After the cursor was closed, I run the query select * from AG.

But the result had updated to salary 200 as given in the cursor.

Help file says modifications is not allowed in the static cursor. But I am able to update the data using static cursor.
 

–> My Answer:

MSDN meant that:

“After you create a STATIC Cursor if the table is updated by any other process that update will not be reflected in the Static Cursor rows.”

1. Let’s say your SPID=100 and you read the table with Salary=100.

2. You created a STATIC Cursor and read the above table with Salary=100.

3. Another SPID=105 updated that Salary=200

4. But when you will read the cursor it will still show you Salary=100.

But yes you can update the same table form the Cursor itself as you are doing above. Check this MSDN link for clear info: https://msdn.microsoft.com/en-us/library/windows/desktop/ms678392%28v=vs.85%29.aspx?f=255&MSPPError=-2147217396
 

Ref Link.


Difference between Index and Primary Key – MSDN TSQL forum

August 10, 2015 Leave a comment

–> Question:

What is the difference between the Index and the Primary Key?
 

–> My Answer:

In simple DBMS words:

– A Primary Key is a constraint or a Rule that makes sure to identify a table’s column uniquely and enforces it contains a value, ie. NOT NULL value.

– An Index on the other side is not a constraint, but helps you organize the table or selected columns to retrieve rows faster while querying with SELECT statement.

In SQL Server you can create only one Primary Key, and by-default it creates a Clustered Index on the table with the PK column as the Index key. But you can specify to create Non-Clustered Index with a PK also.

Indexes in SQL Server mainly are:

– Clustered Index

– Non Clustered

… you can specify them as unique or non-unique.

Other type of indexes are:

– ColumnStore

– Filtered

– XML

– Spatial

– Full Text
 

–> Another Answer by Erland:

A primary key is a logical concept. The primary key are the column(s) that serves to identify the rows.

An index is a physical concept and serves as a means to locate rows faster, but is not intended to define rules for the table. (But this is not true in practice, since some rules can only be defined through indexes, for instance filtered indexes.)

In SQL Server a primary key for a disk-based table is always implemented as an index. In a so-called memory-optimized table, the primary key can be implemnted as an index or as a hash.
 

–> Another Answer by CELKO:

PRIMARY KEY is define in the first chapter of the book on RDBMS you are too lazy to read. It is a subset of columns in the table which are all not null and unique in the table. An index is an access method used on the records of a physical file.

Many SQL products use indexes to implement keys; many do not (hashing is a better way for large DB products)
 

Check the video on Primary Keys:

PK Constraint
 

Ref link.


Error Msg 8672, MERGE statement attempted to UPDATE or DELETE the same row more than once – MSDN TSQL forum

August 5, 2015 Leave a comment

–> Question:

I am trying to do update/insert operation using merge statement, but getting below error:

“The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.”

I am using SQL Server 2014. I knew there was a bug in older version of SQL Server (like version 2008). Does this issue still persist in latest version of SQL Server?
 

–> My Answer:
There might be 2 reasons:

1. Your source/target tables might be having DUPLICATES.

2. There might NO DUPLICATES, but you might be using NOLOCK hint? If yes, then your source table might be very big and going through constant changes (INSERT/UPDATE/DELETE), which might be causing PAGE-SPLITS and causing to read single source row TWICE. Try removing NOLOCK and run your MERGE stmt.
 

–> Another Answer by Naomi:

You need to fix the source of the problem, not apply patches. If your source has duplicate rows for the same key used to update, when how would you know which row will be used to update? The simple solution is to eliminate duplicates by either using correct combinations of keys to join or creating an extra key with ROW_NUMBER() approach.
 

Ref Link.


Bug with ISNUMERIC() – MSDN TSQL forum

July 8, 2015 2 comments

–> Question:

Has anyone seen this SQL bug before?

If you use the IsNumeric function with 12345 and replace number 3 with an “e” and no other letter, SQL still thinks it’s numeric.

If ISNUMERIC('12e45') = 1
print 'Is Numeric'
else 
Print 'No'

 

–> My Answer:

Yes, “12e45” is still numeric, notice the small ‘e’ which is a symbol for exponent for representing a big number.

But yes, NUMERIC() function does results incorrect results for some values, like:

SELECT
     ISNUMERIC('123') as '123'
    ,ISNUMERIC('.') as '.' --Period
    ,ISNUMERIC(',') as ',' --Comma

SELECT
     ISNUMERIC('123') as '123'
    ,ISNUMERIC('-') as '-'
    ,ISNUMERIC('+') as '+'
    ,ISNUMERIC('$') as '$'
    ,ISNUMERIC('\') as '\'

… gives you 1 for all these non-numeric values.

After release of SQL Server 2012 and ahead you must be using TRY_PARSE() instead of ISNUMERIC().

Check my blog post on how to use this – Use new TRY_PARSE() instead of ISNUMERIC() | SQL Server 2012
 

–> Answer by CELKO:

The use of E or e for floating point numbers started in the 1950’s with FORTRAN and later in Algol. You wrote “twelve times ten to the forty-fifth power” in the standard notation. The only difference is that some languages have to start with a digit and some can start with the E.

Please look up how floating numbers are displayed. This has been true for over 60 years! The E or e is for “exponent” in the notation that goes back to FORTRAN I.
 

–> Answer by Erland Sommarskog:

In addition to other posts, on SQL2012 or later, you can use try_convert to check if the number is convertible to a specific data type:

SELECT 
CASE WHEN try_convert(int, @str) IS NOT NULL 
       THEN 'Converts' 
       ELSE 'Not convertible' 
END

 

–> Answer by Dan Guzman:

ISNUMERIC isn’t particularly useful for the common use case of checking for a string containing only the digits 0 through 9. Consider a CASE expression instead to get the desired behavior:

CASE WHEN @value LIKE '%[^0-9]%' OR @value = '' THEN 0 ELSE 1 END

 

Ref link.