Hello! I'm new to the forum, nice to meet you all.
I recently started a new position that will require me to use Teradata on a regular basis. Formerly I was a database developer/admin for a company that used SQL Server exclusively. If you are like me or have been in a similar situation, I'd like to hear some of your learning experiences about the major differences you may have came across when switching between DB platforms.
Your response can include anything from differences in SQL syntax that you didn't expect, to tools that you find extremely helpful in your daily Teradata tasks, to differences in performance, indexes, to general best practice tips, to things you have learned that a basic google search or reviewal of the teradata documentation didn't immeidately reveal.
I apologize for the broad topic to start my posting history off but any bits of information you can provide will be immensely helpful. Have a great day!
I am also transitioning from a SQL Server OLTP environment to Teradata. Here are a few points I hope you find helpful.
1. Instances -Teradata is not an instance based product. If you have one Teradata server you have one instance. Make sure the users and management understand this. If there is a need for multiple 'environments' on one server it needs to be done through database names.
2. Managing space - With SQL Server using SAN storage you manage free disk space. Often this involves extending the disk or moving the database to a larger capacity disk. With Teradata the mindset changes to maintain free space within the database itself. You will want to keep a close eye on perm space. Also remember to keep an eye on how much spool space is being used.
3. Statistics - There is no auto update statistics and auto create statistics. Create some scripts to create/update statistics. Also be sure you don't create them on empty tables which get loaded at a later time. My experience is bad stats are worse than no stats.
4. SQL Assistant - There is an option to only run highlighted queries. Very handy if you are used to SQL Server Management Studio. This option will keep you out of trouble. If it isn't enabled SQL Assistant will try to run everything in Window.
5. Permissions - Table permissions remain even after the table is dropped. Very handy in certain situations but there is a possibility of orphaned permissions. If objects are being deleted permantently be sure to revoke permissions first.
Sorry to raise an old issue, and appreciate the advise you listed here TDMerie. Are there any resources or test databases to work through, somewhat like Adventure Works samples, data warehousing, etc.. I learn best by seeing and doing, so videos are a huge help for me.
Any further advice is greatly appreciated, Happy Thanksgiving :)