Ms Sql Server Technology Discussions

[Darling999]

[quote name='9pardhu' timestamp='1357749792' post='1303078358']
Does any one know how shrinking works?

Shrinking of log files or data files?

how the data will be fragmented.... evaarinikina indetailed ga telisthe chepandi..i have few common questions
[/quote]
em kaavali neku?

[deals2buy]

[quote name='gundugadu' timestamp='1357742025' post='1303077695']
baaaaa nuvvu super baaaaaaaa

aaa script compponent post matram adurs

nuvvu developer leka DBA na
[/quote]
Developer
[quote name='DARLING...' timestamp='1357744468' post='1303077836']
[b] SSIS Component Samples List[/b]

[color=#333333][font=Arial, Tahoma, Verdana][size=3]This is a directory of all the SSIS Components with links to the introductory articles and samples I have created to help understand them. If no link exists it means the article hasn’t been written yet! If you want to hurry me along on one component that I haven’t done, please leave a request in the comments.[/size][/font][/color]
[b] SSIS Control Flow and Data Flow Items with Examples[/b]



[b] [url="http://www.bimonkey.com/support/ssis-component-samples-list/"]http://www.bimonkey....t-samples-list/[/url][/b]



[b] Data Flow[/b]

[b] Data Flow Sources[/b]

[color=#333333][font=Arial, Tahoma, Verdana][size=3][b]ADO .NET / DataReader Source[/b] – [url="http://www.bimonkey.com/2009/05/the-ado-net-source-and-sql-in-the-script-task/"]Article[/url] – Samples: 2005 – [url="http://www.bimonkey.com/uploads/componentreview/ADO%20NET%20Source%20Component%20Basics.dtsx"]2008[/url][/size][/font][/color]
[color=#333333][font=Arial, Tahoma, Verdana][size=3][b]Excel Source[/b] – [url="http://www.bimonkey.com/2009/05/the-excel-source-and-connection-manager-the-basics/"]Article[/url] – Samples: [url="http://www.bimonkey.com/uploads/componentreview/excelsource2005.zip"]2005[/url] – [url="http://www.bimonkey.com/uploads/componentreview/excelsource2008.zip"]2008[/url][/size][/font][/color]
[color=#333333][font=Arial, Tahoma, Verdana][size=3][b][url="http://www.bimonkey.com/support/ssis-component-samples-list/#"]Flat File[/url] Source[/b] – [url="http://www.bimonkey.com/2009/05/the-flat-file-connection-manager-and-source/"]Article[/url] – Samples: 2005 - [url="http://www.bimonkey.com/uploads/componentreview/flatfilesource.zip"]2008[/url][/size][/font][/color]
[color=#333333][font=Arial, Tahoma, Verdana][size=3][b]OLE DB Source[/b] – [url="http://www.bimonkey.com/2009/04/the-ole-db-source-component-the-basics/"]Article[/url] – Samples: 2005 – [url="http://www.bimonkey.com/uploads/componentreview/OLEDB%20Source%20Component%20Basics.dtsx"]2008[/url][/size][/font][/color]
[color=#333333][font=Arial, Tahoma, Verdana][size=3][b]Raw File Source[/b] – [url="http://www.bimonkey.com/2009/04/the-raw-file-source-and-destination/"]Article[/url] – Samples: 2005 – [url="http://www.bimonkey.com/uploads/componentreview/Raw%20File%20Components%20Basics.dtsx"]2008[/url][/size][/font][/color]
[color=#333333][font=Arial, Tahoma, Verdana][size=3][b]XML Source[/b] – [url="http://www.bimonkey.com/2009/04/the-xml-source-component-the-basics/"]Article[/url] – Samples: 2005 – [url="http://www.bimonkey.com/uploads/componentreview/XMLSource.zip"]2008[/url][/size][/font][/color]
[b] Data Flow Transformations[/b]

[color=#333333][font=Arial, Tahoma, Verdana][size=3][b]Aggregate[/b] – [url="http://www.bimonkey.com/2009/05/the-aggregate-transformation/"]Article[/url] – Samples: [url="http://www.bimonkey.com/uploads/componentreview/Aggregate%20Transformation%20Basics%202005.dtsx"]2005[/url] – [url="http://www.bimonkey.com/uploads/componentreview/Aggregate%20Transformation%20Basics%202008.dtsx"]2008[/url][/size][/font][/color]
[color=#333333][font=Arial, Tahoma, Verdana][size=3][b]Audit[/b] – [url="http://www.bimonkey.com/2009/05/the-audit-transformation/"]Article[/url] – Samples: [url="http://www.bimonkey.com/uploads/componentreview/Audit%20Transformation%20Basics%202005.dtsx"]2005[/url] – [url="http://www.bimonkey.com/uploads/componentreview/Audit%20Transformation%20Basics%202008.dtsx"]2008[/url][/size][/font][/color]
[color=#333333][font=Arial, Tahoma, Verdana][size=3][b]Character Map[/b] – Article – Samples: 2005 – 2008[/size][/font][/color]
[color=#333333][font=Arial, Tahoma, Verdana][size=3][b]Conditional Split[/b] – [url="http://www.bimonkey.com/2009/06/the-conditional-split-transformation/"]Article[/url] – Samples: [url="http://www.bimonkey.com/uploads/componentreview/Export%20Column%20Transformation%20Basics%202005.dtsx"]2005[/url] – 2008[/size][/font][/color]
[color=#333333][font=Arial, Tahoma, Verdana][size=3][b]Copy Column[/b] – [url="http://www.bimonkey.com/2009/06/the-copy-column-transformation/"]Article[/url] – Samples: [url="http://www.bimonkey.com/uploads/componentreview/Copy%20Column%20Transformation%20Basics%202005.dtsx"]2005[/url] – 2008[/size][/font][/color]
[color=#333333][font=Arial, Tahoma, Verdana][size=3][b]Data Conversion[/b] – [url="http://www.bimonkey.com/2009/06/the-data-conversion-transformation/"]Article[/url] – Samples: [url="http://www.bimonkey.com/uploads/componentreview/Data%20Conversion%20Transformation%20Basics%202005.zip"]2005[/url] – 2008[/size][/font][/color]
[color=#333333][font=Arial, Tahoma, Verdana][size=3][b][url="http://www.bimonkey.com/support/ssis-component-samples-list/#"]Data Mining[/url] Query[/b] – Article – Samples: 2005 – 2008[/size][/font][/color]
[color=#333333][font=Arial, Tahoma, Verdana][size=3][b]Derived Column[/b] – [url="http://www.bimonkey.com/2009/08/the-derived-column-transformation/"]Article[/url] – Samples: [url="http://www.bimonkey.com/uploads/componentreview/Derived%20Column%20Transformation%20Basics%202005.dtsx"]2005[/url] – 2008[/size][/font][/color]
[color=#333333][font=Arial, Tahoma, Verdana][size=3][b]Export Column[/b] – [url="http://www.bimonkey.com/2009/06/the-export-column-transformation/"]Article[/url] – Samples: [url="http://www.bimonkey.com/uploads/componentreview/Export%20Column%20Transformation%20Basics%202005.dtsx"]2005[/url] – 2008[/size][/font][/color]
[color=#333333][font=Arial, Tahoma, Verdana][size=3][b]Fuzzy Grouping [/b]- [url="http://www.bimonkey.com/2009/11/the-fuzzy-grouping-transformation/"]Article[/url] – Samples: [url="http://www.bimonkey.com/uploads/componentreview/Fuzzy%20Grouping%20Transformation%20Basics%202005.dtsx"]2005[/url] – 2008[/size][/font][/color]
[color=#333333][font=Arial, Tahoma, Verdana][size=3][b]Fuzzy Lookup[/b] – [url="http://www.bimonkey.com/2009/06/the-fuzzy-lookup-transformation/"]Article[/url] – Samples: [url="http://www.bimonkey.com/uploads/componentreview/Fuzzy%20Lookup%20Transformation%20Basics%202005.zip"]2005[/url] – 2008[/size][/font][/color]
[color=#333333][font=Arial, Tahoma, Verdana][size=3][b]Import Column[/b] – [url="http://www.bimonkey.com/2009/09/the-import-column-transformation/"]Article[/url] – Samples: [url="http://www.bimonkey.com/uploads/componentreview/Import%20Column%20Transformation%20Basics%202005.dtsx"]2005[/url] – 2008[/size][/font][/color]
[color=#333333][font=Arial, Tahoma, Verdana][size=3][b]Lookup[/b] – Article – Samples: 2005 – 2008[/size][/font][/color]
[color=#333333][font=Arial, Tahoma, Verdana][size=3][b]Merge[/b] – [url="http://www.bimonkey.com/2009/07/the-merge-transformation/"]Article[/url] – Samples: [url="http://www.bimonkey.com/uploads/componentreview/Merge%20Transformation%20Basics%202005.dtsx"]2005[/url] – 2008[/size][/font][/color]
[color=#333333][font=Arial, Tahoma, Verdana][size=3][b]Merge Join[/b] – [url="http://www.bimonkey.com/2010/10/the-merge-join-transformation/"]Article[/url] – Samples: [url="http://www.bimonkey.com/uploads/componentreview/Merge%20Join%20Transformation%20Basics%202005.dtsx"]2005[/url] – 2008[/size][/font][/color]
[color=#333333][font=Arial, Tahoma, Verdana][size=3][b]Multicast[/b] – [url="http://www.bimonkey.com/2009/07/the-multicast-transformation/"]Article[/url] – Samples: [url="http://www.bimonkey.com/uploads/componentreview/Multicast%20Transformation%20Basics%202005.dtsx"]2005[/url] – 2008[/size][/font][/color]
[color=#333333][font=Arial, Tahoma, Verdana][size=3][b]OLE DB Command[/b] – Article – Samples: 2005 – 2008[/size][/font][/color]
[color=#333333][font=Arial, Tahoma, Verdana][size=3][b]Percentage Sampling[/b] – [url="http://www.bimonkey.com/2009/06/the-percentage-sampling-transformation/"]Article[/url] – Samples: [url="http://www.bimonkey.com/uploads/componentreview/Aggregate%20Transformation%20Basics%202005.dtsx"]2005[/url] – 2008[/size][/font][/color]
[color=#333333][font=Arial, Tahoma, Verdana][size=3][b]Pivot[/b] – [url="http://www.bimonkey.com/2009/06/the-pivot-transformation/"]Article[/url] – Samples: [url="http://www.bimonkey.com/uploads/componentreview/Pivot%20Transformation%20Basics%202005.dtsx"]2005[/url] – 2008[/size][/font][/color]
[color=#333333][font=Arial, Tahoma, Verdana][size=3][b]Row Count[/b] – [url="http://www.bimonkey.com/2009/08/the-row-count-transformation/"]Article[/url] – Samples: [url="http://www.bimonkey.com/uploads/componentreview/Row%20Count%20Transformation%20Basics%202005.dtsx"]2005[/url] – 2008[/size][/font][/color]
[color=#333333][font=Arial, Tahoma, Verdana][size=3][b]Row Sampling[/b] – [url="http://www.bimonkey.com/2010/06/the-row-sampling-transformation/"]Article[/url] – Samples: [url="http://www.bimonkey.com/uploads/componentreview/Row%20Sampling%20Transformation%20Basics%202005.dtsx"]2005[/url] – 2008[/size][/font][/color]
[color=#333333][font=Arial, Tahoma, Verdana][size=3][b]Script Component[/b] – Article: [url="http://www.bimonkey.com/2009/09/the-script-transformation-part-2-as-a-source/"]Source[/url] / [url="http://www.bimonkey.com/2009/09/the-script-transformation-part-1-a-simple-transformation/"]Transformation[/url] / [url="http://www.bimonkey.com/2009/09/the-script-transformation-part-3-as-a-destination/"]Destination[/url] – Samples: [url="http://www.bimonkey.com/uploads/componentreview/Script%20Transformation%20Basics%202005.dtsx"]2005[/url] – 2008[/size][/font][/color]
[color=#333333][font=Arial, Tahoma, Verdana][size=3][b]Slowly Changing Dimension[/b] – Article: [url="http://www.bimonkey.com/2009/07/the-slowly-changing-dimension-transformation-part-1/"]Type 1 (basics)[/url] – Samples: [url="http://www.bimonkey.com/uploads/componentreview/Slowly%20Changing%20Dimension%20Transformation%20Basics%202005.zip"]2005[/url] – 2008 | [url="http://www.bimonkey.com/2009/08/the-slowly-changing-dimension-transformation-part-2-type-2-dimensions/"]Type 2[/url] – Samples: [url="http://www.bimonkey.com/uploads/componentreview/Slowly%20Changing%20Transformation%20Type%202%202005.zip"]2005[/url] – 2008[/size][/font][/color]
[color=#333333][font=Arial, Tahoma, Verdana][size=3][b]Sort[/b] – [url="http://www.bimonkey.com/2009/07/the-sort-transformation/"]Article[/url] – Samples: [url="http://www.bimonkey.com/uploads/componentreview/Sort%20Transformation%20Basics%202005.dtsx"]2005[/url] – 2008[/size][/font][/color]
[color=#333333][font=Arial, Tahoma, Verdana][size=3][b]Term Extraction[/b] – [url="http://www.bimonkey.com/2009/07/the-term-extraction-transformation/"]Article[/url] – Samples: [url="http://www.bimonkey.com/uploads/componentreview/Term%20Extraction%20Transformation%20Basics%202005.zip"]2005[/url] – 2008[/size][/font][/color]
[color=#333333][font=Arial, Tahoma, Verdana][size=3][b]Term Lookup[/b] – Article – Samples: 2005 – 2008[/size][/font][/color]
[color=#333333][font=Arial, Tahoma, Verdana][size=3][b]Union All[/b] – Article – Samples: 2005 – 2008[/size][/font][/color]
[color=#333333][font=Arial, Tahoma, Verdana][size=3][b]Unpivot[/b] – [url="http://www.bimonkey.com/2009/07/the-unpivot-transformation/"]Article[/url] – Samples: [url="http://www.bimonkey.com/uploads/componentreview/Unpivot%20Transformation%20Basics%202005.zip"]2005[/url] – 2008[/size][/font][/color]
[b] Data Flow Destinations[/b]

[color=#333333][font=Arial, Tahoma, Verdana][size=3][b]Data Mining Model Training[/b] – Article – Samples: 2005 – 2008[/size][/font][/color]
[color=#333333][font=Arial, Tahoma, Verdana][size=3][b]DataReader Destination[/b] – Article – Samples: 2005 – 2008[/size][/font][/color]
[color=#333333][font=Arial, Tahoma, Verdana][size=3][b]Dimension Processing[/b] – Article – Samples: 2005 – 2008[/size][/font][/color]
[color=#333333][font=Arial, Tahoma, Verdana][size=3][b]Excel Destination[/b] – Article – Samples: 2005 – 2008[/size][/font][/color]
[color=#333333][font=Arial, Tahoma, Verdana][size=3][b]Flat File Destination[/b] – Article – Samples: 2005 – 2008[/size][/font][/color]
[color=#333333][font=Arial, Tahoma, Verdana][size=3][b]OLE DB Destination[/b] – Article – Samples: 2005 – 2008[/size][/font][/color]
[color=#333333][font=Arial, Tahoma, Verdana][size=3][b]Partition Processing[/b] – Article – Samples: 2005 – 2008[/size][/font][/color]
[color=#333333][font=Arial, Tahoma, Verdana][size=3][b]Raw File Destination[/b] – [url="http://www.bimonkey.com/2009/04/the-raw-file-source-and-destination/"]Article[/url] – Samples: 2005 – [url="http://www.bimonkey.com/uploads/componentreview/Raw%20File%20Components%20Basics.dtsx"]2008[/url][/size][/font][/color]
[color=#333333][font=Arial, Tahoma, Verdana][size=3][b]Recordset Destination[/b] – Article – Samples: 2005 – 2008[/size][/font][/color]
[color=#333333][font=Arial, Tahoma, Verdana][size=3][b]SQL Server Destination[/b] – Article – Samples: 2005 – 2008[/size][/font][/color]
[color=#333333][font=Arial, Tahoma, Verdana][size=3][b]SQL Server Mobile Destination[/b] – Article – Samples: 2005 – 2008[/size][/font][/color]
[/quote]
[quote name='erragulabi' timestamp='1357745875' post='1303077939']
Does any one working on AWS cloud? and migration from SQL server 2008 to AWS?
If yes, so can you share your experiences
[/quote]AWS ante endi mama?
[quote name='9pardhu' timestamp='1357749792' post='1303078358']
Does any one know how shrinking works?

Shrinking of log files or data files?

how the data will be fragmented.... evaarinikina indetailed ga telisthe chepandi..i have few common questions
[/quote]ya..good topic..naaku kuda teluskovalani undi

[ghazni]

na oka sql query undi adi performance tuning cheyali miku telisina suggestions chepandi....... Execution plan chudali kani na query agitey adi telustadi.... nadi casette reel tiriginattu tirugutuney undi.......

actual ga where condition lo oka value peditey it is good adey IN peti 2 values istey na saami ranga adi eptiki agatledu......

miru query ela tune chestaro chepandi......

for your information I dont have access to Database engine Tuning advisor

[chelsea]

[quote name='ghazni' timestamp='1357770260' post='1303081328']
na oka sql query undi adi performance tuning cheyali miku telisina suggestions chepandi....... Execution plan chudali kani na query agitey adi telustadi.... nadi casette reel tiriginattu tirugutuney undi.......

actual ga where condition lo oka value peditey it is good adey IN peti 2 values istey na saami ranga adi eptiki agatledu......

miru query ela tune chestaro chepandi......

for your information I dont have access to Database engine Tuning advisor
[/quote]

Estimated execution plan chudu ba ekkada cost expensive vunte dani meda chudu malli Profiler run chesi chudu Index ela vunnai ani chudu

[ghazni]

[quote name='chelsea' timestamp='1357770970' post='1303081397']
Estimated execution plan chudu ba ekkada cost expensive vunte dani meda chudu malli Profiler run chesi chudu Index ela vunnai ani chudu
[/quote]

idi production server so naku inka anni permissions ivaledu so i can not use profiler....

Inkoti nenu values change chestey query run ayindi ee sari it suggested to create an index...... ala suggest chesina index create cheyocha......

[deals2buy]

[quote name='ghazni' timestamp='1357771602' post='1303081437']
idi production server so naku inka anni permissions ivaledu so i can not use profiler....

Inkoti nenu values change chestey query run ayindi ee sari it suggested to create an index...... ala suggest chesina index create cheyocha......
[/quote]

suggest chesindi cheyyamani kaadu kaani when u repeatedly keep on checking the column it would always be better idea to create an index on that column for fast data retrieval & bettter performance

[deals2buy]

[b] sp_Blitz - Security[/b]


http://www.sqlservercentral.com/articles/SQLServerCentral/95693/

[deals2buy]

[b] SQL Server T-SQL Tuning -TVF and Scalar Functions[/b]

A UDF is very convenient for centralising business logic as we can specify a set of business logic in one UDF which references multiple stored procedures and ad-hoc queries. However, they can lead to significant performance degradation due to their demands on the CPU

[b] Table-Valued Functions (TVF)[/b]

As a general concept, when using a TVF an inner join with an object without sufficient indexing will result in a TVF scan which is very similar to an Index Scan or a Table Scan.
As an alternative we could create temp tables with the appropriate clustered indexes or non-clustered indexes as below:[list]
[*]Create the appropriate temp tables.
[*]Create sufficient clustered and non clustered according to your T-SQL Query.
[*]Insert all data coming from the TVF into temp tables.
[*]Replace each TVF by this temp table and all relevant columns as well.
[*]Delete the temp tables at the end of the query.
[/list]
Note the performance advantage of temp tables over table variables in this scenario since (as noted in the previous article) table variables do not support indexes.

[b]Examples :[/b] :

[i]Create a TVF :[/i]

[CODE]
use [Workshops]
go
alter FUNCTION Dep_Salaries1
(
@empid int
)
RETURNS @table table
(
Department int,
Salary_Max int,
Salary_Min int
)
AS
BEGIN
declare @Department int = (select S.deptid from Employees s where s.empid=@empid)
insert into @table
SELECT S.deptid , max (Salary) , MIN(Salary) FROM Employees s inner join Departments T ON S.deptid =T.deptid group by S.deptid having S.deptid =@Department
RETURN
END
GO
[/CODE]

[i]Poor T-SQL Practice1 using a TVF :[/i]

[i][CODE]
alter procedure Unperformant_SP1
@empid int
as
begin
select T.deptid as department_name , s.* from Dep_Salaries1 (@empid )S inner join Departments T ON S.Department =T.deptid
end
[/CODE][/i]

[i]Replacing TVF using Temp tables :[/i]

[i][CODE]
alter procedure Performant_SP1
@empid int
as
begin
create table #table
(
Department int,
Salary_Max int,
Salary_Min int
)
create clustered index #table_index1 on #table (Department)
insert into #table select * from Dep_Salaries1 (@empid )
select T.deptid as department_name , s.* from #table S inner join Departments T ON S.Department =T.deptid
end
[/CODE][/i]

You should still performance test both approaches with your specific queries and data to determine which the best for your case.
[b] Scalar Functions Within Selected Columns[/b]

Scalar functions are very convenient for determining aggregative values ,cumulative values and differentiated values within stored procedures or ad-hoc queries, but they come with a performance hit especially when used with large volumes of data since a scalar function will be executed for each record.

[b]Alternatives to Scalar Functions[/b]
[b]Temp Tables[/b]
Temp tables can be used, although the situation is a little different from the TVF scenario as we are looking to dispense entirely with the scalar function and instead use their internal T-SQL code directly.

[b]Persisted deterministic computed columns[/b]
Persisted deterministic computed column values are not recomputed every time they are selected but are computed one time when they are created. Thus they can add a distinct performance advantage for T-SQL queries as they reduce the processing overhead. This feature can be added by following the below steps[list=1]
[*]Adding a new computed column to store the results of scalar function.
[*]Enabling the persisted feature of this computed column.
[*]Place the appropriate indexes on the column either as key column or include column.
[/list]
But bear in your mind that persisted feature has some restrictive limitations such as:[list]
[*]Computed columns should not to use any aggregate functions of other records.
[*]Computed columns should not use functions that call external system procedures.
[*]Computed columns should not use any functions of other fields of other tables.
[*]It is generally better to use System provided functions such as Convert , Cast ,Replace etc and not UDFs created by developers since UDFs often contradict the deterministic feature.
[/list]
This only applies to the persisted feature , but to able to add an index on these computed columns , they should be also Precise columns by making sure they are calculated of precise data types like Int ,Bigint and Datetime and not imprecise columns such as decimal types. If the datatypes are not precise ,you can just add these columns to the ‘include columns’ part of the index and not ‘keys column’ part.

[b]Use Scheduled Update Jobs[/b]

If it is not possible to use persisted deterministic computed columns, you can create normal columns and create scheduled update jobs on them to update them with the output of scalar functions and then use these columns in your T-SQL query instead of a scalar function as below:

[i]Create a Scalar Function :[/i]

[CODE]
use [Workshops]
go
create FUNCTION Salary_Tax
(
@empid int
)
RETURNS float
AS
BEGIN
declare @salary int = (select (S.salary-100) from Employees s where s.empid=@empid)
RETURN @salary
END
GO
[/CODE]

[CODE]
--Poor T-SQL Practice using the Scalar function
Select empid ,dbo.Salary_Tax (empid) as 'SalaryWithTax' from Employees
[/CODE]

[i]Replace a Scalar function with a Temp table : [/i]

[i][CODE]
Create Table #temp (Empid int primary key clustered , Salary_Tax float)
Create nonclustered index #temp_Index1 on #temp (Empid ) include (Salary_Tax )
insert into #temp select Empid ,(Salary-100) as salary_Tax from Employees
select * from #temp
[/CODE][/i]

[i]Replace a Scalar function using persisted deterministic computed columns : [/i]

[i][CODE]
ALTER TABLE dbo.Employees ADD Salary_Tax AS Salary-100 PERSISTED
Create nonclustered index Employees_Index1 on Employees (Empid, Salary_Tax )
select empid ,Salary_Tax from Employees
[/CODE][/i]

[i]Replace a Scalar function using Scheduled jobs :[/i]

[i][CODE]
ALTER TABLE dbo.Employees ADD Salary_Tax1 float, update_flag bit
ALTER TABLE dbo.Employees ADD CONSTRAINT DF_Employees_update_flag DEFAULT 0 FOR update_flag
[/CODE][/i]

Schedule the below DML update by an appropriate frequency according to your workload

[CODE]
Update Employees set Salary_Tax1=Salary-100 WHERE UPDATE_Flag=0
[/CODE]

Then you can include the below select query within your stored procedure.

[CODE]
select empid , Salary_Tax1 from Employees
[/CODE]

[deals2buy]

I execute the following T-SQL
[CODE]
DECLARE @A INT,@B INT, @C INT

SET @A = 2

SET @B = 3

SET @C = 4

SELECT (@A + @-@C AS 'Result #1'

SELECT @A + (@B-@C) AS 'Result #2'

SELECT (@A + @ * @C AS 'Result #3'

SELECT @A + (@B * @C) AS 'Result #4'
[/CODE]
The question is what are the results returned by each of the 4 select statements.

[Desamudhuru]

BIDS 2005 use chestuna. Data ni table nundi FlatFile CSV ki send chestuna ...... leading '0's ravadam ledu. Output CSV format ee kavali tharvatha nenu danni oka chota upload cheyali.
data in DB
039
002
abc

output FF
39
2
abc

Naku leading 0's kavali..... Data type in DB is Varchar(3)

DT_SWTR try chesa REPLICATE try chesa, Data Conversion try chesa. ......


Ink emaina solutions vunte chepandi plzzzz...

[deals2buy]

[quote name='Desamudhuru' timestamp='1357917425' post='1303091124']
BIDS 2005 use chestuna. Data ni table nundi FlatFile CSV ki send chestuna ...... leading '0's ravadam ledu. Output CSV format ee kavali tharvatha nenu danni oka chota upload cheyali.
data in DB
039
002
abc

output FF
39
2
abc

Naku leading 0's kavali..... Data type in DB is Varchar(3)

DT_SWTR try chesa REPLICATE try chesa, Data Conversion try chesa. ......


Ink emaina solutions vunte chepandi plzzzz...
[/quote]


script task raayi..to add leading zeroes..that would be the best solution always

[deals2buy]

[b] An Introduction to Database Design[/b]

http://www.sqlservercentral.com/articles/Database+Design/72054/

freshers ki very useful

[deals2buy]

[b] Using MDX to Calculate Both Values and Percentages for Analysis Services[/b]


[b] Problem[/b]

When creating pie charts using data from Analysis Services, having the MDX query calculate and return the percentages along with the counts or sums is extremely efficient. In this tip, we walk through an example of how this can be done.
[b] Solution[/b]

The solution presented in this tutorial will utilize a calculated member using the WITH keyword to perform the percentage calculation. In the code sample provided below, we will get the percentage distribution and sum of the measure Internet Sales-Sales Amount sliced by the Gender attribute of the Customer dimension. The following screenshot shows the aforementioned measure and dimensional attributes as they appear in Query Designer.
[img]http://www.mssqltips.com/tipimages2/2849_Figure1.jpg[/img]
In the following code sample we define the name of our calculated member to be [Measures].[Percentage] in line 2. Next in lines 3 and 4 we define the MDX division operation to calculate the percentage. In line 5, the number format is defined to return 4 digits to the right of the decimal point. In line 8, we select the calculated member measure [Measures].[Percentage] and the existing measure [Measures].[Internet Sales Amount]. Line 9 of the sample code selects all of the values in the Customer dimension's Gender attribute.

WITH
MEMBER [Measures].[Percentage] AS
[Measures].[Internet Sales Amount]/
([Customer].[Gender].currentmember.parent,[Measures].[Internet Sales Amount]),
FORMAT_STRING = '0.0000'
SELECT
NON EMPTY {[Measures].[Percentage], [Measures].[Internet Sales Amount] } ON COLUMNS,
NON EMPTY {([Customer].[Gender].[Gender].ALLMEMBERS ) } ON ROWS
FROM [Adventure Works]
Please note that it might be intuitive to want to switch lines 3 and 4 so the seemingly higher value (the total sales) is in the denominator after the forward slash (/) and the smaller value (the amount per gender) is in the numerator before the forward slash.
The results from the sample query above as they appear in SQL Server Management Studio are shown below. This result set allows for the use of one query in Reporting Services to provide the percentages needed for a pie chart and the actual values that could be placed in a corresponding table.

[img]http://www.mssqltips.com/tipimages2/2849_Figure2.jpg[/img]

[deals2buy]

[b] SQL Server Tacklebox[/b]


[url="http://www.red-gate.com/products/sql_Monitor/offers/monitor_tacklebox.htm?utm_source=ssc&utm_medium=weblink&utm_content=rodneybook200908&utm_campaign=sqlmonitor"][b]Download eBook (PDF)[/b][/url]: Free
[url="http://www.amazon.com/SQL-Server-Tacklebox-Rodney-Landrum/dp/1906434255"][b]Printed Book[/b][/url]: $29.99
[b][url="http://www.simple-talk.com/simplepod/SQLServerTacklebox.epub"]Download the ePub version:[/url][/b] Free

[deals2buy]

[quote name='Kaarthikeya' timestamp='1357826012' post='1303083896']
I execute the following T-SQL
[CODE]
DECLARE @A INT,@B INT, @C INT

SET @A = 2

SET @B = 3

SET @C = 4

SELECT (@A + @-@C AS 'Result #1'

SELECT @A + (@B-@C) AS 'Result #2'

SELECT (@A + @ * @C AS 'Result #3'

SELECT @A + (@B * @C) AS 'Result #4'
[/CODE]
The question is what are the results returned by each of the 4 select statements.
[/quote]


[b]Answer: [/b]1,1,20,14
[b]Explanation: [/b]You need to understand the precedence in mathematical operations. Parenthesis are handled before other operations. Multiplication and division come before addition and subtraction, which are handled left to right.
Reference: SQL Saturdays Math and SQL Server - [url="http://www.sqlservercentral.com/links/1427054/289375"]http://brandietarvin.livejournal.com/75705.html[/url]