by Derek Du

Recently, we were asked to improve the performance of some SQL queries that run for 15 seconds and 30 seconds. At first, we were not sure whether the performance could be improved, given that there were about 80 million data pieces in the database. I have to admit that SQL Server is really powerful as eventually we reduced the time to 2 seconds and 6 seconds!

During the proces...

by Jacky Xu

Before a new version of SQL server is released, we always implement data paging through the transact-SQL function “ROW_NUMBER” and it always needs a nested SQL script. Microsoft released new scripts “OFFSET” and “FETCH” for data paging functions. Here are two sample queries for different SQL server version paging function.

First, we query all dat...

by Derek Du

The other day I found an interesting way to test a SQL Server database connection. It is very easy, and quicker than what I usually do. For those of you who like moving pictures, here is a video tutorial.

To test a database connection, I usually make sure a port is open by using the Telnet command and then use a tool like SQL Command Line, or SQL Server Management Studio, ...

by Derek Du

Today, when moving databases via backup and restoring, I had an issue of re-creating User Mappings on the new databases. An error, “User Already Exists in the Current Database,” prevents me from adding any mapping to the existing databases. Below is a solution:
This script shows the existing users of the selected database:
EXEC sp_change_users_login ‘Repor...

by Sheila Zhang
In a previous blog, I started explaining how to perform complex data transfers. When you need to manipulate the source data much to fit the destination -- such as the ETL process (extract – transform – load) -- you can use SSDT (SQL Server Data Tool) to develop a SSIS solution.
The development language of SSIS is different from other development languages that need you ...

by Kristi Feng
Here are the three most interesting articles I read this week:

by Emma Tsai
As a business analyst, I not only work with clients to get the requirements, but also perform functional testing for software applications. Sometimes, I need to work with SQL server to do database query and compare the results while testing. Recently, I found using Windows functions to perform data analysis computations in SQL server is pretty handy.
Basically, Windows aggregate funct...

By Emma Tsai

For the past few weeks, I introduced some integration solutions we created for our clients and gave a high-level overview about how these different applications work together and how the user interface looks. In addition to system integration, we also have strong web application development techniques and experience.   
This ROI (Return on Investment) tool we developed for ...

By Jacky Xu
Recently, we have a project that needed to print a report through SQL server reporting service. It’s my first time to work on the SQL server reporting service, so I had to spend some time researching this. I think reporting service is a great tool to show reports, and easy to use. Below are some steps to print reports:
1. Create a new Windows Form solution in VS2010.
2.&n...

By Derek Du
When dealing with firewalls, you must have a clear understanding of which port your application is using.
Today I took it for granted that a SQL Server named instance such as \SQLEXPRESS is sharing the same 1433 port with the default SQL Server instance. Later after I found out it didn’t work, it finally occurred to me that two processes cannot listen to the same port.
Anyways...