Archive
What does “on-disk structure” means w.r.t Indexes in SQL Server – MSDN TSQL forum
–> 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.
SQL Error – Unable to generate a temporary class (result=1), error CVT1108, error CS1583
Few days back I got an email from one of this blog reader mentioning that he was facing issues while Installing SQL Server, and was getting following error:
SQL Server Setup has encountered the following error:
Unable to generate a temporary class (result=1).
error CVT1108: cannot open C:\Users\DELL\AppData\Local\Temp\RESAC3F.tmp for writing
error CS1583: ‘c:\Users\DELL\AppData\Local\Temp\CSCAC2F.tmp’ is not a valid Win32 resource file
By the above error its evident that the user does not have sufficient rights to the Temporary location mentioned above “C:\Users\DELL\AppData\Local\Temp”.
As the SQL Server installer EXE is trying to extract the compressed files to this Temp location before actually installing SQL Server on your machine. But it looks like due to some reason the use account does not have access to this location, thus throwing error mentioned above.
–> Now the resolution for this is to, either:
1. Re-extract the contents to an another drive where you have full permission.
– or –
2. To set proper rights for the User on the Temp location. Go to the above Temp location and Right-click on the Temp folder, and select Properties, go to Securities tab. Under the “Groups or user names:” section just check if your user name is there. Select the user name and make sure it should have “Full Control” selected under the “Permissions for Administrators”. If it’s not then click on the Edit button and provide Full rights to the user account.
How exactly STATIC CURSOR works internally – MSDN TSQL forum
–> 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
–> 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:
Ref link.
Informatica – ERROR: Workflow [FOLDER_NAME:WORKFLOW_NAME[version 1]] is disabled. Please check the Integration Service log for more information.
Today I got an email alert for the Informatica Workflow that was working properly suddenly gave following error:
ERROR: Workflow [FOLDER_NAME:WORKFLOW_NAME[version 1]] is disabled.
Please check the Integration Service log for more information.
The Workflow was not getting kicked off and there was nothing else showing up in the logs other than this error.
I did a bit of research and as the error message indicates I found that one setting might be enabled that can disable the Workflow.
–> I opened the Workflow Designer, opened the Workflow in the Designer window. Right-click on the Workflow Designer canvas and this opens up following window, which shows up as below:
… here as you can see that the setting “Runtime Options, Disabled” is checked/enabled (circled, above). This means that the Workflow is currently in disabled state. Just un-check this option and save it by clicking on the Apply button.
Now re-run the Workflow and it should run now without any issues.
Current Visitors
StatCounter …since April 2012
Leisure blog: Creek & Trails
- An error has occurred; the feed is probably down. Try again later.
SQL with Manoj 