Reg: SqlServer Data Type

Hi
Can you please explain the difference between Text and nText data type in
SqlServer and at what situation we have to use this.
Siva.ntext is national text and it is used for storing unicode data.
text is used for storing non-unicode data
ntext occupies twice the size for storing the same data as in text
ntext supports all characters defined in unicode standard.|||and this is from BOL.. if you had not already seen it.
Using Unicode Data
The Unicode specification defines a single encoding scheme for most
characters widely used in businesses around the world. All computers
consistently translate the bit patterns in Unicode data into characters usin
g
the single Unicode specification. This ensures that the same bit pattern is
always converted to the same character on all computers. Data can be freely
transferred from one database or computer to another without concern that th
e
receiving system will translate the bit patterns into characters incorrectly
.
One problem with data types that use 1 byte to encode each character is that
the data type can only represent 256 different characters. This forces
multiple encoding specifications (or code pages) for different alphabets suc
h
as European alphabets, which are relatively small. It is also impossible to
handle systems such as the Japanese Kanji or Korean Hangul alphabets that
have thousands of characters.
Each Microsoft? SQL Server? collation has a code page that defines what
patterns of bits represent each character in char, varchar, and text values.
Individual columns and character constants can be assigned a different code
page. Client computers use the code page associated with the operating syste
m
locale to interpret character bit patterns. There are many different code
pages. Some characters appear on some code pages, but not on others. Some
characters are defined with one bit pattern on some code pages, and with a
different bit pattern on other code pages. When you build international
systems that must handle different languages, it becomes difficult to pick
code pages for all the computers that meet the language requirements of
multiple countries. It is also difficult to ensure that every computer
performs the correct translations when interfacing with a system using a
different code page.
The Unicode specification addresses this problem by using 2 bytes to encode
each character. There are enough different patterns (65,536) in 2 bytes for
a
single specification covering the most common business languages. Because al
l
Unicode systems consistently use the same bit patterns to represent all
characters, there is no problem with characters being converted incorrectly
when moving from one system to another. You can minimize character conversio
n
issues by using Unicode data types throughout your system.
In Microsoft SQL Server, these data types support Unicode data:
nchar
nvarchar
ntext
Note The n prefix for these data types comes from the SQL-92 standard for
National (Unicode) data types.
Use of nchar, nvarchar, and ntext is the same as char, varchar, and text,
respectively, except that:
Unicode supports a wider range of characters.
More space is needed to store Unicode characters.
The maximum size of nchar and nvarchar columns is 4,000 characters, not
8,000 characters like char and varchar.
Unicode constants are specified with a leading N: N'A Unicode string'.
All Unicode data uses the same Unicode code page. Collations do not control
the code page used for Unicode columns, only attributes such as comparison
rules and case sensitivity.|||SivaprakashShanmugam a écrit :
> Hi
> Can you please explain the difference between Text and nText data type in
> SqlServer and at what situation we have to use this.
NTEXT is encoded as UNICODE wich use 2 byte per chars
TEXT is encoded as ASCII wich use 1 byte per chars
This mean :
1) the amount of data is double while using NTEXT so the perfs whyle
retrieving data will be slower
2) only NTEXT an be use simultaneously by many alphabets like latin,
gr, cyrilic, hebrew, japanese, chinese...
A +

> Siva.
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

referencing inserted and deleted tables with sp_executeSql

Hi everyone. Thanks in advance to anyone who might be able to shed some light on this situation.

I have a trigger in which the following SQL code exists.

SET @.tempInserted = N'SET @.dummy = (SELECT '+@.cftColumnName+' FROM INSERTED)'
EXEC sp_executeSQL @.tempInserted, N'@.dummy varchar(255) output', @.dummy=@.tempAddress output

When the trigger executes I receive the following error message...

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'INSERTED'.

My question is, is it possible to in some way reference the INSERTED and DELETED tables using sp_executeSQL?I was able to replicate your problem:
-- Set Option Value
-- -------- ----
-- textsize 64512
-- language us_english
-- dateformat mdy
-- datefirst 7
-- arithabort SET
-- nocount SET
-- remote_proc_transactions SET
-- ansi_null_dflt_on SET
-- ansi_warnings SET
-- ansi_padding SET
-- ansi_nulls SET
-- concat_null_yields_null SET

create table #Tmp(f1 int, f2 char(1))
go
create trigger TmpTrigger on Tmp
FOR DELETE, INSERT, UPDATE
AS
BEGIN
declare @.tempInserted nvarchar(100)
, @.cftColumnName nvarchar(100)
, @.tempAddress nvarchar(100)
set @.cftColumnName = 'f2'
SET @.tempInserted = N'SELECT @.dummy = ' + @.cftColumnName + ' FROM inserted'
SET @.tempInserted = N'select * From #Tmp'
EXEC sp_executeSQL @.tempInserted, N'@.dummy varchar(255) output', @.dummy=@.tempAddress output
select @.tempAddress
END
go
insert into #Tmp values(2,'B')
insert into Tmp values(1,'A')
select * From Tmp
go
drop table #Tmp
drop table Tmp
go

The only thing I can figure out is that since inserted and deleted are special temp tables they are not available to the new process created during the execution of sp_executeSQL.|||Thanks Paul. I ended up taking a different approach that essentially enabled me to achieve what I was attempting to do. Instead of directly referencing the INSERTED and DELETED tables I first create another set of temp tables to which I copy all the records from INSERTED and DELETED. I can then make a reference to these temporary tables during the execution of sp_executeSQL.

Not perfect but it works =).|||A workable solution is better than nothing working! You can always go back and change your code once everything runs end to end.

Reduce Records Shown From Dimension

Hi There,

I'm relatively new to AS2005, so you'll have to excuse me if there is a simple solution that has been overlooked.

Here is the situation - We have a fact table, linked to several dimensions - one of them being a generic date dimension used by a number of fact tables. The Date Dimension has all dates ranging from 1901 to 2100, but the fact table I am querying just has records from 2005 to present. Once the cube is processed and I open the cube in Excel (for example) it lists every date from the Date Dimension, is there any way to limit which dates are processed into the cube to those that just appear in the fact table (in essence - what would be an Inner Join in TSQL).

Hope this makes sense,

Regards,

Tobias

Are you using the dimension on rows or columns? Or as a filter?

If it is rows or columns you change the setting to not show empty values.

If it is a filter, you could create a set that only contains only those dates that has rows. Regardless, you could probably remove a lot of dates from the time dimension that is not used by any fact table and/or create a view that adds dates as time goes by.|||

Instead of telling the cube (via the data source view) to read directly from the time dimension table, we tend to use db views that restrict the members to only those members that appear in the fact table. You can change the dsv to read from the view and you will then only get dates that appear in the fact table.

However, with the time dimension, you want to make sure you fill in all dates of a year -- if you don't you can't really do year-over-year comparisons reliably. So, perhaps you can have the view return all dates from your time dimension table that start AFTER the first date in your fact table. You can use similar logic to restrict the dates on the high end as well.

|||

So does this mean, for each fact table - you have a different view set up?

We have about 15-20 cubes that we want to set up which all use the time dimension, with varying volumes of historical data from each of the source systems. Is it best practise to then limit the data returned by the dimension for each group of cubes?

I was hoping that there would be a technique/feature that prevented 'redundant' (i.e. rows that do not have a related fact record) dimension members from appearing in the cube, that way we could begin to build the suite of cubes in a smaller number of projects and reduce administrative overheads (by having several Time Dim views for different date ranges for example)

Regards,

Tobias

|||

Well, our one view looks at all of our various fact tables to determine which entries are needed.

The problem you have is that Excel will list out all dimension members, so I'm not sure what the best solution is for you. But I don't think there is magic property that you can tell Excel to list only dimension members that are only for a particular cube.

|||Is it Excel 2007 you use? Because then you can create a named set that only contains dates that are non empty, has rows in the fact table connected to it. I′m not sure if Excel 2003 can use sets.|||We're using Excel 2003. shame there isn't a feature that does that.|||

Even in Excel 2003 pivot table, you have the option to hide the members that do not have data.

As you are using time dimension in different measure groups, the best practice would be to create hierarchy in the date dimension like Year ==> Month ==> Date levels that would make the navigation easier for the users. There are lot of articles on ways to handle it. Pl refer to BOL and newsgroups.