Results 1 to 5 of 5

Thread: DataType Alias

  1. #1
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1

    Unanswered: DataType Alias

    Hello looking at using the data type alias feature of sql server -wondering if there is any drawbacks to using these, are the indexes slower or anything like that which I should seek to avoid.

    CREATE TYPE (Transact-SQL)

  2. #2
    Join Date
    Sep 2011
    Posts
    71
    By using an alias, we can keep the application configuration the same, but tell the operating system to look somewhere else for the database. One proviso is that once an alias is created, it is set at the operating system level. That means if we have two applications which connect to MySQLServer on that computer, once the alias is created, both will be re-pointed based on the alias. You can create an alias using one of two utilities:

    SQL Server Configuration Manager
    SQL Server Client Network Utility

    SQL Server Configuration Manager

    Let's start by looking at how to do it using SQL Server Configuration Manager. In the left pane of SQL Server Configuration Manager, if you expand the SQL Native Client Configuration folder, there is a subfolder called Aliases (see Figure 1). If we click on this subfolder, we'll see any aliases that have been defined for the system shown in the right pane. In most cases, there won't be any defined. To create a new alias, right-click on Aliases and choose New Alias... from the pop-up menu.
    then
    A dialog window comes up (Figure 2) which allows us to specify our new alias. In the Alias Name property, you specify the name the application uses. For instance, if it connects to a SQL Server called MypaulServer, that's what you enter in here. You can also enter an alias that appears to point to a named instance, such as MypaulServer\MyInstance. You can then specify the server where the database really is, to include configuring the protocol, the server name, and the port. Clicking the OK button will create the alias. In the case of , I'm creating an alias called MypaulServer which points to my local server's default instance.

    http://www.mssqltips.com/tipimages/1620_CreatingAliases_02.jpg

    If the alias is pointed correctly to the new location, a successful connection should be established. In SSMS, I was opening a new connection for Object Explorer.
    shows that the connection was successfully made using the alias. Note that as far as SSMS is concerned, the server name is the alias.

    SQL Server Client Network Utility

    If you haven't installed the SQL Server 2005/2008 client tools, you can still create an alias using the SQL Server Client Network Utility. This has come installed automatically on every operating system from Windows 2000 on. To bring up the utility, click on Start, then Run, and run cliconfg.exe. To view or create aliases, click on the Alias tab (Figure 6). As you can see from Figure 6, aliases created by SQL Server Configuration Manager can be seen by the SQL Server Client Network Utility. The reverse is also true.
    http://www.mssqltips.com/tipimages/1...Aliases_07.jpg

    you can see orihinal article in below link
    How to setup and use a SQL Server alias

  3. #3
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1

    - ?

    Not sure you understood what I meant, hope that link is correct

    Meaning Creating a datatype Foo = Varchar(50) (Alias DataType?) new data type.

  4. #4
    Join Date
    Sep 2011
    Posts
    71
    OK ,Nate1 i thought now i understand you ,
    you need disadvantages using ADD,Correct? if yes take these notes carefully
    Advantages:

    Reusable and Sharable, the ADT is reusable within the database it's created in, if you want it across all your DBs then create it in the model database

    Enforcement, the ADT will enforce the characteristics, length and nullability of it's base type, and can be used to enforce development standards

    Disallows implicit conversions, ADTs cannot be CAST or CONVERTed

    Simplicity, as with other languages, ADTs lend simplicity to development and maintenance

    Data Hiding

    Disadvantages:

    Unmodifiable, ADTs cannot be directly modified, you must DROP and reCREATE them. Note that you can still ALTER the tables they're in to another base type or ADT, then DROP and reCREATE them, then ALTER the tables back (or just CREATE a new one to alter them to).

    No Tools, ADTs must be created with a direct query using the CREATE statement (sp_addtype is deprecated and shouldn't be used).

    Table variables are not supported
    Also final notes of using them
    Note

    If an alias type is created in the model database, it exists in all new user-defined databases. However, if the data type is created in a user-defined database, the data type exists only in that user-defined database.
    NoteNote

    When alias data types are dropped, they can still be referenced by table variables later in the batch in which they were dropped.

  5. #5
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1

    Thats Good

    Thanks, sorry been doing this on the sideline, so any help is appreciated. Casting sounds like a problem, though an inline function may fix that.

    Will have to be sure of the datatype before I create them - to save time, but thank you very helpful.


    " When alias data types are dropped, they can still be referenced by table variables later in the batch in which they were dropped. " - though If I create them carefully and meaningfully shouldn't be an issue.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •