What do Clustered and Non clustered index actually mean?

[Origin]: https://stackoverflow.com/questions/1251636/what-do-clustered-and-non-clustered-index-actually-mean

I have a limited exposure to DB and have only used DB as an application programmer. I want to know about Clustered and Non clustered indexes. I googled and what I found was :

A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages. A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

What I found in SO was What are the differences between a clustered and a non-clustered index?.

Can someone explain this in plain English?

With a clustered index the rows are stored physically on the disk in the same order as the index. Therefore, there can be only one clustered index.

With a non clustered index there is a second list that has pointers to the physical rows. You can have many non clustered indexes, although each new index will increase the time it takes to write new records.

It is generally faster to read from a clustered index if you want to get back all the columns. You do not have to go first to the index and then to the table.

Writing to a table with a clustered index can be slower, if there is a need to rearrange the data.

In SQL Server row oriented storage both clustered and nonclustered indexes are organized as B trees.

enter image description here

(Image Source)

The key difference between clustered indexes and non clustered indexes is that the leaf level of the clustered index is the table. This has two implications.

  1. The rows on the clustered index leaf pages always contains something for each of the (non sparse) columns in the table (either the value, or a pointer to the actual value).
  2. The clustered index is the primary copy of a table.

Non clustered indexes can also do point 1 by using the INCLUDE clause (Since SQL Server 2005) to explicitly include all non key columns but they are secondary representations and there is always another copy of the data around (the table itself).

CREATE TABLE T
(
A INT,
B INT,
C INT,
D INT
)

CREATE UNIQUE CLUSTERED INDEX ci ON T(A,B)
CREATE UNIQUE NONCLUSTERED INDEX nci ON T(A,B) INCLUDE (C,D)

The two indexes above will be nearly identical. With the upper level index pages containing values for the key columns A,B and the leaf level pages containing A,B,C,D

There can be only one clustered index per table, because the data rows themselves can be sorted in only one order.

The above quote from SQL Server books online causes much confusion

In my opinion it would be much better phrased as.

There can be only one clustered index per table, because the leaf level rows of the clustered index are the table rows.

The books online quote is not incorrect but you should be clear that the “sorting” of both non clustered and clustered indices is logical not physical. If you read the pages at leaf level by following the linked list and read the rows on the page in slot array order then you will read the index rows in sorted order but physically the pages may not be sorted. The commonly held belief that with a clustered index the rows are always stored physically on the disk in the same order as the index keyis false.

This would be an absurd implementation. For example if a row is inserted into the middle of a 4GB table SQL Server does not have to copy 2GB of data up in the file to make room for the newly inserted row .

Instead a page split occurs. Each page at the leaf level of both clustered and non clustered indexes has the address (File:Page) of the next and previous page in logical key order. These pages need not be either contiguous or in key order.

e.g. the linked page chain might be 1:2000 <-> 1:157 <-> 1:7053

When a page split happens a new page is allocated from anywhere in the filegroup (from either a mixed extent, for small tables, or a non empty uniform extent belonging to that object or a newly allocated uniform extent). This might not even be in the same file if the file group contains more than one.

The degree to which the logical order and contiguity differs from the idealised physical version is the degree of logical fragmentation.

In a newly created database with a single file I ran the following.

CREATE TABLE T
  (
     X TINYINT NOT NULL,
     Y CHAR(3000) NULL
  );

CREATE CLUSTERED INDEX ix
  ON T(X);

GO

--Insert 100 rows with values 1 - 100 in random order
DECLARE @C1 AS CURSOR,
        @X  AS INT

SET @C1 = CURSOR FAST_FORWARD
FOR SELECT number
    FROM   master..spt_values
    WHERE  type = 'P'
           AND number BETWEEN 1 AND 100
    ORDER  BY CRYPT_GEN_RANDOM(4)

OPEN @C1;

FETCH NEXT FROM @C1 INTO @X;

WHILE @@FETCH_STATUS = 0
  BEGIN
      INSERT INTO T (X)
      VALUES        (@X);

      FETCH NEXT FROM @C1 INTO @X;
  END

Then checked the page layout with

SELECT page_id,
       X,
       geometry::Point(page_id, X, 0).STBuffer(1)
FROM   T
       CROSS APPLY sys.fn_PhysLocCracker( %% physloc %% )
ORDER  BY page_id

Results were all over the place. The first row in key order (with value 1 – highlighted with arrow below) was on nearly the last physical page.

enter image description here

Fragmentation can be reduced or removed by rebuilding or reorganising an index to increase the correlation between logical order and physical order.

After running

ALTER INDEX ix ON T REBUILD;

I got the following

enter image description here

If the table has no clustered index it is called a heap.

Non clustered indexes can be built on either a heap or a clustered index. They always contain a row locator back to the base table. In the case of a heap this is a physical row identifier (rid) and consists of three components (File:Page:Slot). In the case of a Clustered index the row locator is logical (the clustered index key).

For the latter case if the non clustered index already naturally includes the CI key column(s) either as NCI key columns or INCLUDE-d columns then nothing is added. Otherwise the missing CI key column(s) silently get added in to the NCI.

SQL Server always ensures that the key columns are unique for both types of index. The mechanism in which this is enforced for indexes not declared as unique differs between the two index types however.

Clustered indexes get a uniquifier added for any rows with key values that duplicate an existing row. This is just an ascending integer.

For non clustered indexes not declared as unique SQL Server silently adds the row locator in to the non clustered index key. This applies to all rows, not just those that are actually duplicates.

The clustered vs non clustered nomenclature is also used for column store indexes. The paper Enhancements to SQL Server Column Stores states

Although column store data is not really “clustered” on any key, we decided to retain the traditional SQL Server convention of referring to the primary index as a clustered index.

Advertisements

Delete file containing invalid characters in windows

[Origin]: https://serverfault.com/questions/95581/delete-file-containing-invalid-characters-in-windows

I have some files that contain colon character in the filename (eg. 1d67c0d23e859ed4a259749e4a720d9e:default-sink). When I try to remove them from command line with the command:

c:\backup> del /F *.*

I get “The system cannot find the file specified.” for each file. If I specify an individual file:

c:\backup> del /F "1d67c0d23e859ed4a259749e4a720d9e:default-sink"

I get “The filename, directory name, or volume label syntax is incorrect.”. If I try to use rd on the parent folder I get

How can I remove these files?

This worked for me:

  1. Ran chkdsk /f
  2. Rebooted PC
  3. Then I was able to select and delete the file

Close all the files, if any opened, that are saved in the drive which is containing such files. Now, Open the drive properties.

“My Computer” -> “Right Click on the this drive” > properties

Then, go to “Tools” tab and click “Check now” under ‘Error checking’ label.

enter image description hereClick here for larger image

This will unmount your drive and will scan the complete drive for errors, eventually it will delete such files with illegal names. On finishing it will show a messages saying some files were fixed.

I successfully deleted such files from my external hard disk.

IE: Prevent confirming dialog box when using window.close() to close a main window

[Origin]: https://blogs.msdn.microsoft.com/rextang/2008/10/17/ie-prevent-confirming-dialog-box-when-using-window-close-to-close-a-main-window/


Been busying on investigating Dynamics CRM 4 Web Application and Outlook VSTO Add-In development for the past weeks and not update blog much. It’s time to do a post now.

Little but useful trick when toying with IE windows.

When there is only a parent IE window and you load some pages with javascript “window.close();” that trying to close the window, in IE there will be a confirming dialog box asking if one really want to close the IE window. This often causes problems if you want to host your IE in a winform, and here are some simple and quick workaround to overcome this.

   1: <HTML>
   2: <HEAD>
   3: <TITLE>Close window without prompt</TITLE>
   4: <SCRIPT LANGUAGE="JavaScript">
   5: <!--
   6: function realClose()
   7: {
   8:     var win=window.open("","_top","","true");
   9:     win.opener=true;
  10:     win.close();
  11: }
  12: //-->
  13: </SCRIPT>
  14: </HEAD>
  15: <BODY>
  16: Close window without prompt
  17: <FORM><INPUT TYPE="button" VALUE="Close ME!" onClick="realClose()"></FORM>
  18: </BODY>
  19: </HTML>

The trick is to open an empty page in self window (the “_top”), and then close the opened window in javascript to fool IE that he is closing a child window. this worked without problems.

Further more, if you don’t have ways to modify the page you are opening, thus not able to embed the realClose() function to the page, maybe in a WinForm that hosting a IE window and actually you don’t know what page you are going to open, maybe this way would work.

   1: <HTML>
   2: <HEAD>
   3: <TITLE>Close window without prompt</TITLE>
   4: <SCRIPT LANGUAGE="JavaScript">
   5: <!--
   6: function realClose()
   7: {
   8:     var win=window.open("","_top","","true");
   9:     win.opener=true;
  10:     //win.close();
  11:     win.realclosefunc();
  12: }
  13: window.realclosefunc = window.close;
  14: window.close = realClose;
  15: //-->
  16: </SCRIPT>
  17: </HEAD>
  18: <BODY>
  19: Close window without prompt
  20: <FORM><INPUT TYPE="button" VALUE="Close ME!" onClick="javascript: window.close();"></FORM>
  21: </BODY>
  22: </HTML>
  23:  

in this way, when in WinForm, try to manipulate DOM of the hosting IE window, and try to insert the piece of code in the head part of page, then change the default window.close function to our self-made function to close the window. using this way won’t need to do a string replacement of the downloaded html contents to replace window.close() to our close function. little bit tidy, but still doable…

For WinForm hosting pattern, using .net 2.0 WinForm WebBrowser control won’t give you that magic WindowClosing event to prevent IE closing in your WinForm. There are articles introducing hooking DWebBrowserEvents2 interface to get this event working with WebBrowser control, but for no reason it just not working.

So I just tried to use COM Interop way to bridge the ActiveX IE control to .net by using aximp.exe command, and by doing this found that the WindowClosing event is working and able to detect the IE close before that confirming dialog box showed up, and then to cancel the IE close action inside the event and close my WinForm. this should be the easiest way to accomplish this task I think.

SSRS 2008 R2 – SSRS 2012 – ReportViewer: Reports are blank in Safari and Chrome

[Origin]: https://stackoverflow.com/questions/5968082/ssrs-2008-r2-ssrs-2012-reportviewer-reports-are-blank-in-safari-and-chrome/7043409#7043409

I migrated our reporting services from version 2008 to another server version 2008 R2. In version 2008 the reports work fine on Safari. The new version 2008 R2 the reports do not show up at all. All I see is the parameter section and then the report is blank. Same in Chrome. According to Microsoft Safari IS supported if in a limited fashion. The reports are not complex. In fact I created a report that only had a line on it to see if it would show up in Safari but no, that report is completely blank as well. Did anyone make SSRS reports viewable on Safari? Do I have to mess with some kind of a configuration setting?

Ultimate solution (works in SSRS 2012 too!)

Append the following script to the following file (on the SSRS Server)

C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportManager\js\ReportingServices.js
function pageLoad() {    
    var element = document.getElementById("ctl31_ctl10");
    if (element) 
    {
        element.style.overflow = "visible"; 
    }
}

Note: As azzlak noted, the div’s name isn’t always ctl31_ctl10. For SQL 2012 tryctl32_ctl09 and for 2008 R2 try ctl31_ctl09. If this solution doesn’t work, look at the HTML from your browser to see if the script has worked properly changing the overflow:auto property to overflow:visible.


Solution for ReportViewer control

Insert into .aspx page (or into a linked .css file, if available) this style line

#reportViewer_ctl09 {
  overflow:visible !important;
 }

Reason

Chrome and Safari render overflow:auto in different way respect to IE.

SSRS HTML is QuirksMode HTML and depends on IE 5.5 bugs. Non-IE browsers don’t have the IE quirksmode and therefore render the HTML correctly

The HTML page produced by SSRS 2008 R2 reports contain a div which has overflow:autostyle, and it turns report into an invisible report.

<div id="ctl31_ctl10" style="height:100%;width:100%;overflow:auto;position:relative;>

I can see reports on Chrome by manually changing overflow:auto to overflow:visible in the produced webpage using Chrome’s Dev Tools (F12).


I love Tim’s solution, it’s easy and working.

But there is still a problem: any time the user change parameters (my reports use parameters!) AJAX refreshes the div, the overflow:auto tag is rewritten, and no script changes it.

This technote detail explains what is the problem:

This happens because in a page built with AJAX panels, only the AJAX panels change their state, without refreshing the whole page. Consequently, the OnLoad events you applied on the <body> tag are only fired once: the first time your page loads. After that, changing any of the AJAX panels will not trigger these events anymore.

User einarq suggested this solution:

Another option is to rename your function to pageLoad. Any functions with this name will be called automatically by asp.net ajax if it exists on the page, also after each partial update. If you do this you can also remove the onload attribute from the body tag

So wrote the improved script that is shown in the solution.

How to get a date in YYYY-MM-DD format from a TSQL datetime field?

[Origin]: https://stackoverflow.com/questions/889629/how-to-get-a-date-in-yyyy-mm-dd-format-from-a-tsql-datetime-field

How do I retrieve a date from SQL Server in YYYY-MM-DD format? I need this to work with SQL Server 2000 and up. Is there a simple way to perform this in SQL Server or would it be easier to convert it programatically after I retrieve the result set?

I’ve read the CAST and CONVERT on Microsoft Technet, but the format I want isn’t listed and changing the date format isn’t an option.

SELECT CONVERT(char(10), GetDate(),126)

Limiting the size of the varchar chops of the hour portion that you don’t want.

Starting with SQL Server 2012 (original question is for 2000):

SELECT FORMAT(GetDate(), 'yyyy-MM-dd')
SELECT convert(varchar, getdate(), 100) -- mon dd yyyy hh:mmAM

SELECT convert(varchar, getdate(), 101) -- mm/dd/yyyy – 10/02/2008                  

SELECT convert(varchar, getdate(), 102) -- yyyy.mm.dd – 2008.10.02           

SELECT convert(varchar, getdate(), 103) -- dd/mm/yyyy

SELECT convert(varchar, getdate(), 104) -- dd.mm.yyyy

SELECT convert(varchar, getdate(), 105) -- dd-mm-yyyy

SELECT convert(varchar, getdate(), 106) -- dd mon yyyy

SELECT convert(varchar, getdate(), 107) -- mon dd, yyyy

SELECT convert(varchar, getdate(), 108) -- hh:mm:ss

SELECT convert(varchar, getdate(), 109) -- mon dd yyyy hh:mm:ss:mmmAM (or PM)

SELECT convert(varchar, getdate(), 110) -- mm-dd-yyyy

SELECT convert(varchar, getdate(), 111) -- yyyy/mm/dd

SELECT convert(varchar, getdate(), 112) -- yyyymmdd

SELECT convert(varchar, getdate(), 113) -- dd mon yyyy hh:mm:ss:mmm

SELECT convert(varchar, getdate(), 114) -- hh:mm:ss:mmm(24h)

SELECT convert(varchar, getdate(), 120) -- yyyy-mm-dd hh:mm:ss(24h)

SELECT convert(varchar, getdate(), 121) -- yyyy-mm-dd hh:mm:ss.mmm

SELECT convert(varchar, getdate(), 126) -- yyyy-mm-ddThh:mm:ss.mmm
answered Aug 30 ’16 at 6:53

Is there a way to crack the password on an Excel VBA Project?

[Origin]: https://stackoverflow.com/questions/1026483/is-there-a-way-to-crack-the-password-on-an-excel-vba-project

I’ve been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there’s a lack of documentation… no-one knows the passwords.

Is there a way of removing or cracking the password on a VBA project?

You can try this direct VBA approach which doesn’t require HEX editing. It will work for any files (*.xls, *.xlsm, *.xlam …).

Tested and works on

Excel 2007
Excel 2010
Excel 2013 – 32 bit version.
Excel 2016 – 32 bit version.

Looking for 64 bit version? See https://stackoverflow.com/a/31005696/4342479

how it works

I will try my best to explain how it works – please excuse my english.

  1. The VBE will call a system function to create the password dialog box.
  2. If user enters the right password and click OK, this function returns 1. If user enters the wrong password or click Cancel, this function returns 0.
  3. After the dialog box is closed, the VBE checks the returned value of the system function
  4. if this value is 1, the VBE will “think” that the password is right, hence the locked VBA project will be opened.
  5. The code below swaps the memory of the original function used to display the password dialog with a user defined function that will always return 1 when being called.

using the code

  1. Open the file(s) that contain your locked VBA Projects
  2. Create a new xlsm file and store this code in Module1

    code credited to Siwtom (nick name), a Vietnamese developer
    
    Option Explicit
    
    Private Const PAGE_EXECUTE_READWRITE = &amp;H40
    
    Private Declare Sub MoveMemory Lib "kernel32" Alias "RtlMoveMemory" _
            (Destination As Long, Source As Long, ByVal Length As Long)
    
    Private Declare Function VirtualProtect Lib "kernel32" (lpAddress As Long, _
            ByVal dwSize As Long, ByVal flNewProtect As Long, lpflOldProtect As Long) As Long
    
    Private Declare Function GetModuleHandleA Lib "kernel32" (ByVal lpModuleName As String) As Long
    
    Private Declare Function GetProcAddress Lib "kernel32" (ByVal hModule As Long, _
            ByVal lpProcName As String) As Long
    
    Private Declare Function DialogBoxParam Lib "user32" Alias "DialogBoxParamA" (ByVal hInstance As Long, _
            ByVal pTemplateName As Long, ByVal hWndParent As Long, _
            ByVal lpDialogFunc As Long, ByVal dwInitParam As Long) As Integer
    
    Dim HookBytes(0 To 5) As Byte
    Dim OriginBytes(0 To 5) As Byte
    Dim pFunc As Long
    Dim Flag As Boolean
    
    Private Function GetPtr(ByVal Value As Long) As Long
        GetPtr = Value
    End Function
    
    Public Sub RecoverBytes()
        If Flag Then MoveMemory ByVal pFunc, ByVal VarPtr(OriginBytes(0)), 6
    End Sub
    
    Public Function Hook() As Boolean
        Dim TmpBytes(0 To 5) As Byte
        Dim p As Long
        Dim OriginProtect As Long
    
        Hook = False
    
        pFunc = GetProcAddress(GetModuleHandleA("user32.dll"), "DialogBoxParamA")
    
        If VirtualProtect(ByVal pFunc, 6, PAGE_EXECUTE_READWRITE, OriginProtect) &lt;&gt; 0 Then
    
            MoveMemory ByVal VarPtr(TmpBytes(0)), ByVal pFunc, 6
            If TmpBytes(0) &lt;&gt; &amp;H68 Then
    
                MoveMemory ByVal VarPtr(OriginBytes(0)), ByVal pFunc, 6
    
                p = GetPtr(AddressOf MyDialogBoxParam)
    
                HookBytes(0) = &amp;H68
                MoveMemory ByVal VarPtr(HookBytes(1)), ByVal VarPtr(p), 4
                HookBytes(5) = &amp;HC3
    
                MoveMemory ByVal pFunc, ByVal VarPtr(HookBytes(0)), 6
                Flag = True
                Hook = True
            End If
        End If
    End Function
    
    Private Function MyDialogBoxParam(ByVal hInstance As Long, _
            ByVal pTemplateName As Long, ByVal hWndParent As Long, _
            ByVal lpDialogFunc As Long, ByVal dwInitParam As Long) As Integer
        If pTemplateName = 4070 Then
            MyDialogBoxParam = 1
        Else
            RecoverBytes
            MyDialogBoxParam = DialogBoxParam(hInstance, pTemplateName, _
                               hWndParent, lpDialogFunc, dwInitParam)
            Hook
        End If
    End Function
    

  3. Paste this code in Module2 and run it
    Sub unprotected()
        If Hook Then
            MsgBox "VBA Project is unprotected!", vbInformation, "*****"
        End If
    End Sub
    
  4. Come back to your VBA Projects and enjoy.

How to shrink the transaction log

[Origin]: https://www.mssqltips.com/sqlservertutorial/3311/how-to-shrink-the-transaction-log/

By: 

One thing that I see a lot of administrators ask about is transaction log size and how to truncate it. Log records that are not managed correctly will eventually fill up the disk causing no more modifications to the database. Transaction log growth can occur for a few different reasons. Long running transactions, incorrect recovery model configuration and lack of log backups can grow the log.

Log truncation frees up space in the log file so the transaction log can reuse it. Unless there is some kind of unexpected delay, log truncation will occur automatically after a checkpoint (if the database is in SIMPLE recovery model) or after a log backup (if the database is in FULL or BULK-LOGGED recovery model). MSSQLTips.com offers plenty of tips regarding transaction log truncation, but I’ll show you two ways to shrink the log.

Shrink the log in SQL Server Management Studio

To shrink the log in SSMS, right click the database, choose Tasks, Shrink, Files:

Shrink the log in SQL Server Management Studio

On the Shrink File window, change the File Type to Log. You can also choose to either release unused space, reorganize pages before releasing unused space, or empty file by migrating the data to other files in the same filegroup:

On the Shrink File window, change the File Type to Log.

Shrink the log using TSQL

If the database is in the SIMPLE recovery model you can use the following statement to shrink the log file:

DBCC SHRINKFILE (AdventureWorks2012_log, 1)

Replace AdventureWorks2012_log with the logical name of the log file you need shrunk and change 1 to the number of MB you want the log file shrunk to.

If the database is in FULL recovery model you could set it to SIMPLE, run DBCC SHRINKFILE, and set back to FULL if you don’t care about losing the data in the log.

ALTER DATABASE AdventureWorks2012
SET RECOVERY SIMPLE
GO
DBCC SHRINKFILE (AdventureWorks2012_log, 1)
GO
ALTER DATABASE AdventureWorks2012
SET RECOVERY FULL

**You can find the logical name of the log file by using the following query:

SELECT name FROM sys.master_files WHERE type_desc = 'LOG'

Another option to shrink the log using the FULL recovery model is to backup the log for your database using the BACKUP LOG statement and then issue the SHRINKFILE command to shrink the transaction log:

BACKUP LOG AdventureWorks2012 TO BackupDevice