SQL

SQL Tips and Tricks for Day to Day Operations

Replace a string in a SQL Server Table Column

UPDATE MY_TABLE

SET PATH = REPLACE(PATH, ‘OLDSTRING’, ‘NEWSTRING’)

Renaming database table column to new name

SP_RENAME ‘TABLENAME.[OLDCOLUMNNAME]’ , ‘[NEWCOLUMNNAME]’, ‘COLUMN’

Renaming database table or SP to new name

SP_RENAME ‘[OLDTABLENAME]’ , ‘[NEWTABLENAME]’

Add one column into existing SQL Table

ALTER TABLE TABLENAME ADD COLUMN_NAME  DATATYPE

Happy Querying :)

Return raw json string in WCF

I want to use a certain format for the output of the data which isn’t (natively) supported by WCF, such as XML or JSON.

Web method return a String together with ResponseFormat = WebMessageFormat.Json. It follow to the JSON encoding of the string.

Corresponds to http://www.json.org all double quotes in the string will be escaped using backslash.

The simplest way to return any kind of data is to change the output type of web method to Stream or Message (from System.ServiceModel.Channels) instead of String.

public Stream GetUsers()

{

//Code Here

return new MemoryStream(Encoding.UTF8.GetBytes(jsonresult));

}

The Web programming model introduced in WCF on .NET Framework 3.5 simplifies this task. The magic happens when the operation return type is of System.IO.Stream (the abstract class, not one of its concrete implementations).

By returning a stream, WCF assumes that the operation wants total control over the bytes that will be returned in the response, and will apply no formatting whatsoever in the data that is returned

Windows Communication Foundation (WCF) can send messages using either buffered or streamed transfers. In the default buffered-transfer mode, a message must be completely delivered before a receiver can read it.

In streaming transfer mode, the receiver can begin to process the message before it is completely delivered.

The streaming mode is useful when the information that is passed is lengthy and can be processed serially. Streaming mode is also useful when the message is too large to be entirely buffered.

Useful Links:

Happy Coding :)

SQL Server

SQL Database Performance Tuning for Developers

Database tuning can be an incredibly challenging task, particularly when working with large-scale data where even the most minor change can have a dramatic impact on performance.

In mid-sized and large companies, most SQL database tuning will be handled by a Database Administrator (DBA). But believe me, there are plenty of developers out there who have to perform DBA-like tasks. Further, in many of the companies I’ve seen that do have DBAs, they often struggle to work well with developers—the positions simply require different modes of problem solving, which can lead to disagreement among coworkers.

On top of that, corporate structure can also play a role. Say the DBA team is placed on the 10th floor with all of their databases, while the developers are on the 15th floor, or even in a different building under a completely separate reporting structure—it’s certainly hard to work together smoothly under these conditions.

In this article, I’d like to accomplish two things:

  1. Provide developers with some developer-side database tuning techniques.
  2. Explain how developers and DBAs can work together effectively.

Read more…

SQL

DBCC CHECKIDENT – manually reset a new current identity value for the identity column

Checks the current identity value for the specified table and, if it is needed, changes the identity value by using DBCC CHECKIDENT

DBCC CHECKIDENT can reset (i.e. reseed) the identity value of the table for the identity column.

For example, Your Table has 5 rows with 5 as last identity. If we want next record to have identity as 15 we need to run following SQL query.

DBCC CHECKIDENT (<YourTableName>, reseed, 14)

If table has to start with an identity of 1 with the next insert then table should be reseeded with the identity to 0.

There are two conditions where it will not work:

  • The current identity value is larger than the maximum value in the table.
  • All rows are deleted from the table.

Reference Link:

Happy Coding :)