Clipboard Ring and Toolbox Snippets

Introduction

Today I show you my favorite feature in visual studio which is Clipboard Ring and Toolbox Snippets.

Implementation

1.    Clipboard Ring

Many times I wish I could go back to something I copied & pasted just a few minutes ago, then to have to go and copy the same text all over again.Turns out that Visual Studio already has this “hidden”feature.  It’s called the Clipboard Ring. 

If you copy a number of items to the clipboard, pressing [Ctrl] + [Shift] + [V] will paste the last text that was copied, but it will also highlight the pasted phrase.

Continue holding down [Ctrl] + [Shift] and press [V] again.  This will cause that highlighted section to cycle through the other items on the clipboard.  This is extremely useful when you have to copy multiple lines of separate text to a new document. 

You can also use [Ctrl] + [Shift] + [Insert]keys instead of [Ctrl] + [Shift] + [V]

2.    Toolbox Snippets

Now, you might be saying, “Great, but what if I want to save a piece of code that I use frequently in multiple places? It means samepiecesof code change in multiple files. I don’t want to have to cycle through the clipboard ring every time.”  Well, there’s also a feature for that! 

If you highlight a section of code (or text) and drag it to the Toolbox, VS will create a "snippet” (not to be confused with code snippets). When u want back that code then youdragged into the VS editor or double click on that snippet it will paste in VS editor.

And last but not leastthese snippets also available in next day.

Screen Sot

SHRINKFILE and TRUNCATE Log File in SQL Server 2008

Introduction

You know there is always an issue - the log file growing very fast and big. If you have plenty of storage, then this might not be a problem for you. Anyway, this is no exception in the latest version of SQL, we still have to do something to truncate and shrink these files.

Implementation

1) Let’s first check the log file size.

SELECT

--DB_NAME(database_id) AS DatabaseName,

--Physical_Name,

Name AS Logical_Name,

(size*8)/1024 SizeMB

FROM

sys.master_files

WHERE

DB_NAME(database_id) = 'tempdb'

GO

Output

2) Now truncate the log file.

USE tempdb;

GO

-- Truncate the log by changing the database recovery model to SIMPLE.

ALTER DATABASE tempdb

SET RECOVERY SIMPLE WITH NO_WAIT;

GO

-- Shrink the truncated log file to 1 MB.

DBCC SHRINKFILE(tempdb_log, 1); --file_name is the logical name of the file to be shrink

GO

-- Reset the database recovery model.

ALTER DATABASE tempdb

SET RECOVERY FULL WITH NO_WAIT;

GO

3) Let’s check the log file size.

SELECT

--DB_NAME(database_id) AS DatabaseName,

--Physical_Name,

Name AS Logical_Name,

(size*8)/1024 SizeMB

FROM

sys.master_files

WHERE

DB_NAME(database_id) = 'tempdb'

GO

Output

Consider the following information when you plan to shrink a file:

• Make a full backup of your database before shrink the database file.
• From setting the database to simple recovery, shrinking the file and once again setting in full recovery, you are in fact losing your valuable log data and will be not able to restore point in time. Not only that, you will also not able to use subsequent log files.
• A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.
• Most databases require some free space to be available for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation. In this case, you should consider increasing the Growth Rate of your Database to keep the performance under control.
• A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. This is another reason not to repeatedly shrink the database.

Reference: http://technet.microsoft.com/en-us/library/ms189493.aspx

Get numeric value from string using TSQL

Introduction
Many times while working on applications, we often land into a situation where we want to get the only numeric values from alpha numeric string. There are some approaches given, like you may achieve it with making a loop and without loop.


Approaches
I have prescribed as a simple query, but you will achieve same by making a function.

1. Get the numbers through loop.

DECLARE @AlphaNumericString VARCHAR(MAX) = 'ABC1DEF2GHI3'

DECLARE @NumericValueIndex INT

SET @NumericValueIndex = PATINDEX('%[^0-9]%', @AlphaNumericString)

WHILE @NumericValueIndex > 0

BEGIN

SET @AlphaNumericString = STUFF(@AlphaNumericString, @NumericValueIndex, 1, '' )

SET @NumericValueIndex = PATINDEX('%[^0-9]%', @AlphaNumericString )

END

SELECT ISNULL(@AlphaNumericString,0) 'Numeric Values'

OUTPUT

2. Get the numbers without loop.

DECLARE @AlphaNumericString VARCHAR(MAX) = 'ABC1DEF2GHI3'

DECLARE @NumericValues VARCHAR(MAX) = ''

SELECT

@NumericValues =

@NumericValues +

CASE WHEN NumericValue like '[0-9]' THEN NumericValue ELSE '' END FROM

(

SELECT

SUBSTRING(@AlphaNumericString,NumericValue,1) NumericValue

FROM

(

SELECT

number NumericValue

FROM

master..spt_values

WHERE 

TYPE = 'p' 

AND number BETWEEN 1 AND LEN(@AlphaNumericString)

) T

) T

SELECT ISNULL(@NumericValues,0) 'Numeric Values'

OUTPUT

Limitation
There is a limitation in 2nd approach, the table “master..spt_values” contains only 2048 rows value. So if you want to get the numbers from the very big text contains above 2048 characters, then this approach is not suitable.
To over come this limitation, you can use “Tally Table” instead of “master..spt_values”. Replace “master..spt_values” with “Tally Table”.
Want to get more information about Tally Table? Click here.


Conclusion
If you have the need of numeric values only from less characters of 2048, then no need to process for “Tally Table”. Just use the “master..spt_values”.

New Date and Time Functions in SQL Server 2008

Introduction

SQL Server 2008 introduces five new date and time functions namely SYSDATETIME, SYSDATETIMEOFFSET, SYSUTCDATETIME, SWITCHOFFSET and TODATETIMEOFFSET.

In this article described new functions in detail.

1)   SYSDATETIME

The SYSDATETIME function basically returns the current system timestamp without the time zone information.

SELECT SYSDATETIME()

GO

SYSDATETIME

------------------------

2011-06-27 16:06:15.9409129

2)   SYSDATETIMEOFFSET

The SYSDATETIMEOFFSET function is much similar to SYSDATETIME function; however it will also get you the time zone information.

SELECT SYSDATETIMEOFFSET()

GO

SYSDATETIMEOFFSET

-------------------------------

2011-06-27 16:07:01.8205935 +05:30

3)   SYSUTCDATETIME

The SYSUTCDATETIME function returns the Greenwich Mean Time (GMT) or the Coordinated Universal Time (UTC). This time is derived from the current local system time and the time zone settings of the server where SQL Server 2008 is running.

SELECT SYSUTCDATETIME()

GO

SYSUTCDATETIME

------------------------

2011-06-27 10:38:25.6239407

4)   SWITCHOFFSET

The SWITCHOFFSET function returns a DATETIMEOFFSET value that is changed from the stored time zone offset to a specified new time zone offset.

SELECT SYSDATETIMEOFFSET(),

       SWITCHOFFSET (SYSDATETIMEOFFSET(), '+00:00')

GO

SYSDATETIMEOFFSET                                            SWITCHOFFSET

-------------------------------                                        -------------------------------------

2011-06-27 16:30:55.7127120 +05:30           2011-06-27 11:00:55.7127120 +00:00

5)   TODATETIMEOFFSET

The TODATETIMEOFFSET function returns a DATETIMEOFFSET value that is translated from a DATETIME2 expression.

SELECT TODATETIMEOFFSET (GETDATE(),'+05:30')

GO

TODATETIMEOFFSET

---------------------------------

2011-06-27 16:26:56.340 +05:30