Load the data for web page using "paging" query

For example, the table of "inv_master_huge" has around 1million rows and you would like to have a functionality for the user to scroll through whatever page that they want. I know this sounds like "no way" or "no user would scroll the data like this". In fact, after filtering the rows by date range for current month, you might still have around 1000 rows.  Anyway, the following query will help in improving the website response time.

The keyword is the "ROW_NUMBER() OVER (ORDER BY ...)".


create proc pr_get_inv_by_page (
    @start_idx int,
    @page_size int
)
as
begin

    select *
    from (
        select
            row_number() over (order by dt) as row_no,
            *
        from inv_master_huge
        -- you may include the date filtering clause here.

    ) as a
    where
        row_no between @start_idx and @start_idx + @page_size

end

Note: in case you allowed the user sort the data by field name, you will have to pass the select statement generated at runtime and pass it to "sp_executesql" stored proc

Sync the SQL user ID

After moving the database from one SQL server to another, the user ID (that has been created in the database) is no longer working. This is mainly due to the "SQL login ID" does not match with the "database user ID". The value that links between these ID-s are call the SID.


Before executing the fixes, you may wonder what has happened to the user ID. Execute the following query:

   use [my_database]
   go
   select * from sysusers where name = 'my_user_name'
   select * from master..syslogins where loginname = 'my_login_name'
   go


From the result, you might notice that the "sid" value in both queries are different. And this is the cause of the login problem.


In MSSQL, there is a system stored procedure that will help you to sync the SID value and it is called "sp_change_users_login".

To get the list of the mismatch user ID, execute this command.

    exec sp_change_users_login 'report'

To sync the database user ID with the SQL login ID:

   exec sp_change_users_login 'update_one', 'my_user_name', 'my_login_name'

Note: you have to replace "my_user_name" and "my_login_name" with the actual values.

Settings the value into a variable

Something that you should know when you are setting the value into a variable. The assignment (i.e., "=" operator) might not work as your expectation. So, try out the script below in the query window:

--declare a table variable
declare @t table (i int)
declare @ii int

-- append 2 record.
insert @t
select 1
union all
select 2

-- show all the records
select *
from @t

-- get the record that match '1'. The expected value in @ii is "1".
select @ii= i
from @t
where i = 1

-- Now, try to get the record that match '0'.
select @ii= i               
from @t
where i = 0                    --<< no record is '0'.

select @ii                    --<< this value is not NULL??!! What is this value??

When "i = 0", there is no record  in @t table variable and "@ii = i" will not be executed. This means the "null" value will not be set to @ii. If you want to get the null value for @ii, then, you should do this:

    select @ii = (select i from @t where i = 0)

Try it out and see the difference.

Inserting records output by the stored procedure

Let's say you have a stored procedure which returns some records from the product table:

create proc pr_test2
as
select prod_code, prod_description, price
from prod
go

So, when you execute pr_test2 stored procedure, the results is the products. In case you want to store these result into a table variable, you may do this:

declare @tb table(
prod_code nvarchar(100),
prod_description nvarchar(100), price money
)

insert into @tb
exec pr_test2

select * from @tb

Stored procedure

What is stored procedure?

• Basically, it is a "module" or "process" that is written in SQL statement and it sits inside database. It is executable by the database server and within the monitor/management of the database server.

Will it run faster if we move the data processing codes from the application into stored procedure?

• Yes. It will be faster because the process is running within the database server. The application sends only one request to the database server and then wait for the response. This reduces the number of data transfer between the database server and the application.

Trigger

Reference:
http://www.mssqltips.com/tip.asp?tip=1591
http://www.mssqltips.com/tip.asp?tip=1380

Database design (I)

Some guidelines for the database design:

1. All tables must have the following fields to store the information of the record creator and the last update information.

- created_on datetime
- created_by nvarchar(255)
- modified_on datetime
- modified_by nvarchar(255)

2. All tables must have a primary key with 1 field only. This is to ensure that the record can be call out easily. (Note: this might violate the normalization design that you have learned from the text book!)

3. Use GUID type for the primary key type instead of integer type - this will ease the page navigation when you are putting the value in the navigation URL. Not easy to be hack by the "normal" user. This also reduce the chance for the user to create "GUID" value manually and insert the record to the table.

4. Reduce the use of self-referencing table design unless you have no other option - this is to simplify the SELECT statement that you are going to write especially when you are developing reports.

5. For generating report, the data should be stored in the predefined reporting table. This splits the development job from the application coding and move the data extraction process into SQL server by using stored procedure. It eases the testing on the data extraction and also speed up the application development (ie., parallelly doing the job).

Get the number of years, months & days for the given 2 dates

declare 
 @dt1 datetime,
 @dt2 datetime,

 @yr int,
 @mth int,
 @day int,

 @yr_count int,
 @mth_count int,
 @day_count int,

 @month_end datetime,
 @month_begin datetime

-- test cases
select 

-- 30 days  <== same yr/month
--@dt1 = '20101202', @dt2 = '20101231' 

-- 1 month <== same yr/month
--@dt1 = '20101201', @dt2 = '20101231'    

-- 1 year.<== same yr & diff mth, start date is 1st.
--@dt1 = '20100101', @dt2 = '20101231'    

-- 2 month, 25 days<== same yr & start date is 1st.
--@dt1 = '20100101', @dt2 = '20100325'   

-- 1 month, 1 day <== diff yr & diff mth
--@dt1 = '20101202', @dt2 = '20110102'   

-- 3 months, 0 day. <== same yr & diff mth
--@dt1 = '20100502', @dt2 = '20100801'   

-- 1 month, 30 day   <== same yr & diff mth
@dt1 = '20101102', @dt2 = '20101231'   

-- 2 months, 0 day   <== diff yr & diff mth
--@dt1 = '20101102', @dt2 = '20110101'   

-- 1yr, 2 months, 0 day   <== diff yr & diff mth
--@dt1 = '20101102', @dt2 = '20120101'   

select 
 @dt1 as 'start date',
 @dt2 as 'end date'

-- get all the differences for further analysis
select
 @yr_count = datediff(year, @dt1,@dt2),
 @mth_count = datediff(month, @dt1,@dt2),
 @day_count = datediff(day, @dt1, @dt2)

-- see if we should retain the year counter.
if (@yr_count - 1 >= 0)
begin
 select @yr_count = @yr_count - 1 
end

-- ensure that the month counter is less than 12.
if @mth_count % 12 <> 0
begin
 select @mth_count = @mth_count % 12
end

-- get first day of the month for the 2nd date.
select
 @month_begin = cast(year(@dt2) as varchar(4)) 
    + case when month(@dt2) > 9 then cast(month(@dt2) as varchar(2))
     else '0' + cast(month(@dt2) as varchar(2))
     end
    + '01'

-- get the last day of the month for the 2nd date.
select 
 @month_end = dateadd(day, -1, dateadd(month, 1, @month_begin))

if (@dt1 = @month_begin) and (@dt2 = @month_end) 
begin
 select 
  @mth_count = @mth_count + 1,
  @day_count = 0

--select @month_begin, @month_end
end
else 
begin
 if @dt1 > @month_begin
  select @month_begin = @dt1

--select @month_begin, @month_end, @dt2

 -- if the end date is the last day of the month
 -- and the start date is the first day of the month,
 -- the day counter will be zero and the month counter increase by 1.
 if (@dt2 = @month_end) and day(@dt1) = 1
 begin
  select 
   @mth_count = @mth_count + 1,
   @day_count = 0

--select @day_count

 end
 else
 begin
  -- update the day counter.
  select @day_count = datediff(day, @month_begin, @dt2) + 1 

 end
end

if  (@mth_count <> 0) and (@mth_count % 12 = 0)
begin

 declare @i int
 select  @i = @yr_count +(@mth_count / 12)

 select 
  @mth_count = 0,
  @yr_count = @i

end

if month(@dt1) <> month(@dt2)
begin

 if day(@dt1) <> 1 and @day_count > 0
  select @day_count = @day_count - 1

end

-- ----------------------------------------------
select
 @yr_count as '# of years',
 @mth_count  as '# of months',
 @day_count as '# of days'