Monday, March 30, 2015

Tuesday, March 24, 2015

SQL Server Data Type Conversion Chart

Illustrates all explicit and implicit data type conversions that are allowed for SQL Server system-supplied data types.
Downloadlink

Friday, March 20, 2015

How to find information about tables, columns, data types, nullable etc.


SELECT 
    t.TABLE_CATALOG
    ,t.TABLE_SCHEMA
    ,t.TABLE_NAME
    ,t.COLUMN_NAME
    ,t.IS_NULLABLE
    ,t.DATA_TYPE
    ,t.CHARACTER_MAXIMUM_LENGTH
FROM  INFORMATION_SCHEMA.COLUMNS t


Tuesday, March 17, 2015

Assign shortcut key to "change connection" in SSMS

1) Right click on the menu banner, and select "Customize"
2) Click on the "Commands" tab.
3) Click on the radio button next to "Toolbar", then scroll through the list to select "SQL Editor"


4) Now you can click on the "Change Connection" control, Once highlighted, click on "Modify 
5) Selection" over on the right, and set it to "Image and Text", and place the & symbol infront of whatever ALT-KEY you want.
6) I have chosen to place the & symbol in for the C, so my shortcut is ALT-C to get to the change connection option.

Friday, March 13, 2015

Simple way to fetch the row count for all tables in a SQL SERVER database

This script uses some sys tables and the object_name function. It allows you to display the rowcount of all the tables in the active database. I've also included the schema name. Remove the 2 joins if you don't need it.

SELECT
    s.name SchemaName
    ,object_name(id) TableName
    ,Rows
FROM sysindexes i
JOIN sys.objects o ON o.object_id = i.id
JOIN sys.schemas s ON s.schema_id = o.schema_id
WHERE indid IN (0, 1)
ORDER BY rows DESC

Monday, March 9, 2015

The IsSorted property must be set to True on both sources of this transformation.


Step 1 
When using the Union All function it is important that the field on which you applied the union is sorted at the data sources using an 'order by'.

Step 2
Go to the Advanced Editor for the OLE DB Source to set IsSorted to True
Step 3
Set SortKeyPosition to 1 for the colum(s) you order on in step 1

Thursday, March 5, 2015

SQL Server Management Studio keyboard shortcuts

Changing Databases

Once you are in a Query Window in SSMS you can use CTRL+U to change the database. When you press this combination, the database combo-box will be selected as shown below. You can then use the UP and DOWN arrow keys to change between databases (or type a character to jump to databases starting with that character) select your database and hit ENTER to return back to the Query Window.


Changing Code Case (Upper or Lower)

When you are writing code you may not bother with using upper or lower case to make your code easier to read. To fix this later, you can select the specific text and hit CTRL+SHIFT+U to make it upper case or use CTRL+SHIFT+L to make it lower case.



Commenting Out Code

When writing code sometimes you need to comment out lines of code. You can select specific lines and hit CTRL+Kfollowed by CTRL+C to comment it out and CTRL+K followed by CTRL+U to uncomment it out.



Indenting Code

As a coding best practice you should to indent your code for better readability. To increase the indent, select the lines of code (to be indented) and hit TAB as many times as you want to increase the indent likewise to decrease the indent again select those lines of code and hit SHIFT+TAB.


Bookmarking Code

When you have hundreds of lines of code, it becomes difficult to navigate. In this case you can bookmark lines to which you would like to return to. Hit CTRL+K followed by CTRL+K again to toggle the bookmark on the line. When you bookmark a line a tiny light blue colored square appears on the left side of the query window to indicate that the line has been bookmarked.

You can press CTRL+K followed by CTRL+N to move to the next bookmarked line from the current cursor location likewise to move back to the last bookmarked line you can hit CTRL+K followed CTRL+P from the current cursor location.
To clear all bookmarks from the current window you can hit CTRL+K followed by CTRL+L.

There might be several bookmarks you have placed in your query window and to manage these easily SSMS provides a Bookmarks window. To launch this window simply hit CTRL+K followed by CTRL+W and you can manage almost every aspect of bookmarking from this window as shown below including renaming the bookmarks.


Search and Find / Replace Text

Sometimes you need to find specific keywords or replace some specific keyword with another keyword. To launch the Quick Find dialog box press CTRL+F or CTRL+H for the Quick Replace dialog box. You can even fine tune your search with other options available in the dialog box or you can bookmark all the lines which contain your search string. To close the dialog box press ESC. You can also press F3 to find the next keyword match.


Goto Line Number

If you know the line number you want to go to you can use CTRL+G to open the Go To Line dialog box and type the line number and press OK (or hit ENTER) to go to that particular line number.


Opening Query Windows and Switching Tabs

Next to open a new query window you can hit CTRL+N or to open a existing script file hit CTRL+O. You can hitCTRL+TAB to switch between open query windows.


More Query Window Shortcuts

Please note apart from the above mentioned shortcut keys these are some standard shortcut keys which work in SSMS as well.
  • CTRL+A to select all the text in the current query window
  • CTRL+C to copy selected text
  • CTRL+V to paste text
  • CTRL+X to cut selected text
  • DEL to delete text
  • CTRL+P to launch Print dialog box
  • CTRL+HOME to go to the beginning of the query window
  • CTRL+END to go to the end of the query window
  • CTRL+SHIFT+HOME to select all text from the current location to the beginning of the query window
  • CTRL+SHIFT+END to select all the text from the current location to the end of the query window
  • ALT+F4 to close SSMS

Hash values are case sensitive

Using hash values is a good idea, but you should be aware this is case sensitive!:
SELECT HashBytes('MD5', 'http://')
      ,HashBytes('MD5', 'HTTP://')












To solve this problem you could upper all the values like this

SELECT HashBytes('MD5', UPPER('http://'))
      ,HashBytes('MD5', UPPER('HTTP://'))




Quick check duplicate column groups in SQL

SELECT
    name, surname, COUNT(*)
FROM
    users
GROUP BY
    name, surname
HAVING 
    COUNT(*) > 1

SSIS - Lookup is case sensitive

A while ago I figured out that the lookup transformation is case sensitive.
I used a lookup to find dimension table members in for my fact table records. This was done on a String business key like ‘AA12BB’. I attached a table for the error output and after running the package I found one record in this table.This record had a business key like ‘Aa12BB’. I searched the dimension table for this missing record and it surprised me, it DID exist but with the following business key: ‘AA12BB’. It seemed the lookup transformation is case sensitive. Next thing I tried was a T-SQL query in the management studio of SQL Server 2005. In the WHERE clause I referred to the business key: ‘Aa12BB’. The query returned the record with business key ‘AA12BB’. Conclusion: SQL Server is not case sensitive but the SSIS lookup component IS case sensitive… Interesting.

Solution:
After some research I found a few solutions for this interesting feature of the lookup transformation. Before I explain these solutions you must know something about the inner working of the lookup component.

A lookup transformation uses full caching by default. This means that the first thing it does on execution, is loading all the lookup data in its cache. When this is done it works as expected, but with case sensitivity.

The solution is to set the CacheType property of the lookup transformation to Partial or None, the lookup comparisons will now be done by SQL Server and not by the SSIS lookup component.
Another solution is to format the data before you do the lookup. You can do this using the T-SQL LOWER() or UPPER() functions. These functions can be used in a query or for example in a derived column SSIS component.

Ways to convert DateTime to VarChar - all styles

DECLARE @now datetime
SET @now = GETDATE()
select convert(nvarchar(MAX), @now, 0) as output, 0 as style 
union select convert(nvarchar(MAX), @now, 1), 1
union select convert(nvarchar(MAX), @now, 2), 2
union select convert(nvarchar(MAX), @now, 3), 3
union select convert(nvarchar(MAX), @now, 4), 4
union select convert(nvarchar(MAX), @now, 5), 5
union select convert(nvarchar(MAX), @now, 6), 6
union select convert(nvarchar(MAX), @now, 7), 7
union select convert(nvarchar(MAX), @now, 8), 8
union select convert(nvarchar(MAX), @now, 9), 9
union select convert(nvarchar(MAX), @now, 10), 10
union select convert(nvarchar(MAX), @now, 11), 11
union select convert(nvarchar(MAX), @now, 12), 12
union select convert(nvarchar(MAX), @now, 13), 13
union select convert(nvarchar(MAX), @now, 14), 14
--15 to 19 not valid
union select convert(nvarchar(MAX), @now, 20), 20
union select convert(nvarchar(MAX), @now, 21), 21
union select convert(nvarchar(MAX), @now, 22), 22
union select convert(nvarchar(MAX), @now, 23), 23
union select convert(nvarchar(MAX), @now, 24), 24
union select convert(nvarchar(MAX), @now, 25), 25
--26 not valid
union select convert(nvarchar(MAX), @now, 100), 100
union select convert(nvarchar(MAX), @now, 101), 101
union select convert(nvarchar(MAX), @now, 102), 102
union select convert(nvarchar(MAX), @now, 103), 103
union select convert(nvarchar(MAX), @now, 104), 104
union select convert(nvarchar(MAX), @now, 105), 105
union select convert(nvarchar(MAX), @now, 106), 106
union select convert(nvarchar(MAX), @now, 107), 107
union select convert(nvarchar(MAX), @now, 108), 108
union select convert(nvarchar(MAX), @now, 109), 109
union select convert(nvarchar(MAX), @now, 110), 110
union select convert(nvarchar(MAX), @now, 111), 111
union select convert(nvarchar(MAX), @now, 112), 112
union select convert(nvarchar(MAX), @now, 113), 113
union select convert(nvarchar(MAX), @now, 114), 114
union select convert(nvarchar(MAX), @now, 120), 120
union select convert(nvarchar(MAX), @now, 121), 121
--122 to 125 not valid
union select convert(nvarchar(MAX), @now, 126), 126
union select convert(nvarchar(MAX), @now, 127), 127
--128, 129 not valid
union select convert(nvarchar(MAX), @now, 130), 130
union select convert(nvarchar(MAX), @now, 131), 131
--132 not valid
order BY style