A General Philosophy for Data Modeling
Posted by Michael Ritacco in Data Modeling on February 26, 2010
The design of any application depends on the foundation of its data model. We must all accept the simple fact that the decisions made during the data modeling phase will determine the overall success of the application.
Before we address any specific data modeling topics or problems, we must talk about the philosophy that will provide the basis for any solutions presented here.
First, it is important to have a good understanding of the rules of performance tuning:
- Tune the design (fix the data model, if it’s not too late)
- Tune the application
- Tune Memory
- Tune IO
- Tune contention
- Tune operating system
What are the data model guidelines that I follow to avoid breaking the performance tuning rules?
- There are no absolutes. All decisions you will make are situational and need to consider the unique application requirements. If you blindly follow a philosophy, including the one presented here, I am sorry to inform you that nothing but luck will be available for you when designing a data model that is both scalable and high performance.
- Design for the database platform. Use the features of the RDBMS you selected, the dream of a data model that works great on all database platforms will never be realized in your life time.
- General purpose ORMs do not create SQL optimized for your application. You will never be lucky enough to be working on an application where the auto generated SQL meets all requirements.
- If the applications fails, it will be either the data model’s or the application design’s fault not the database platform selected.
- While designing the data model visualize all queries that will be run, how new data will be inserted, and the data manipulations that will be done in various use case scenarios.
- You must know how the query optimizer works for the RDBMS platform you are designing for.
- All applications will SELECT, INSERT, UPDATE, DELETE data. Understand what the application needs to do, who is doing it, and how it will be done.
- UPDATE and DELETE statements will always need to search for data, INSERTS not so much.
- Users are more patient when they give data, but not as much when they are selecting data. Generally speaking, if a design decision must be made between INSERT or SELECT performance, error on benefiting the SELECT.
- Advise the development team on how best to use “your” data model, or better yet help them build the data access layer (DAL).
How do I get a Record Set from a Stored Procedure in Oracle
Posted by Michael Ritacco in Database Development, Oracle Database on May 30, 2009
Developers that are familiar with using MS SQL Server eventually get the opportunity to work on a project that is Oracle based. This opportunity sometimes becomes an extremely frustrating time due to the philosophical differences in the approach to database development. I believe it is hard for most developers to transition between the two database platforms, not because one is necessarily better or worse, just because it is too hard for many people to change the way they solve problems once proven methodologies have been established.
These are a few questions that are just about guaranteed to come up during the initial stages of the project:
How do I get a record set from Oracle using a stored procedure?
Using a REF_CURSOR you can return a record set/cursor from a stored procedure.
Why don’t stored procedure work the same as in SQL Server?
In most cases you just use straight SQL and do not need a stored procedure. In Oracle, using cursors and adhoc SQL is the way. In SQL Server, using cursors and adhoc SQL is to be avoided (generally speaking, there are no absolutes). Why you need to use a stored procedure for a SQL statement in Oracle must be fully explored. I am not saying you don’t need one, but the reason is not because that is the way you do it in SQL Server.
Please do not even start coding until you have read the following documents:
Oraclenotes.com is now DBAnotes.com
Posted by Michael Ritacco in Announcements & News on May 3, 2009
If you are looking for Oraclenotes.com, Do Not Panic, you are in the right place. Today we have finally made the switch from Oraclenotes.com to DBAnotes.com. Oraclenotes.com was a stand-alone website since 1999, and we appreciate all of the support we received over the past 10 years.
It was a difficult decision to consolodate the two sites, but we wanted to move forward with one domain dedicated to all things databases. This vision resulted in DBAnotes.com, and we hope you enjoy the new site.
What is a Schema in SQL Server 2005?
Posted by Michael Ritacco in SQL Server on February 21, 2009
What is a Schema?
In Microsoft SQL Server 2005, a schema is a collection of objects adhering to the ANSI SQL-92 standard.
The ANSI SQL-92 standard defines a schema as a collection of database objects that are owned by a single principle and form a single namespace.
All objects within a schema must be uniquely named and a schema must be uniquely named in the database catalog. SQL Server 2005 breaks the link between users and schemas, users do not own objects. Schemas own objects and principles own schemas.
A schema can be owned by either a primary or secondary principle, with the term “principle” meaning any SQL Server entity that can access securable objects.
Principle types that can own schemas:
- Primary
- SQL Server Login
- Database User
- Windows Login
- Secondary
- SQL Server Roles
- Windows Groups
- Default Schemas
Users can now have a default schema assigned using the DEFAULT_SCHEMA option of CREATE USER and ALTER USER commands. If no default schema is supplied for a user then DBO will be used as the default schema.
——————————————————————————–
SQL Server 2005 Schema Objects
- Tables
- Indexes
- Views
- Synonyms
- Stored Procedures
- Triggers
- Functions
- Assemblies
- Types
- Defaults
SQL Server 2008 System Views Map
Posted by Michael Ritacco in SQL Server on February 3, 2009
Download the SQL 2008 System View Map
The Microsoft SQL Server 2008 System Views Map shows the key system views included in SQL Server 2008, and the relationships between them.
Best New Features of SQL 2008
Posted by Michael Ritacco in SQL Server on February 3, 2009
I am getting ready to upgrade an existing SQL 2005 application and have found the following SQL 2008 features worth more exploration:
- INSERT (Transact-SQL Row Constructors) – Allows the ability to add one or more new rows to a table or a view. Seems novel but not sure how useful in practice.
- MERGE Statement. This new Transact-SQL statement performs INSERT, UPDATE, or DELETE operations on a target table based on the results of a join with a source table. Oracle has had this statement for years. I am glad Microsoft has finally added it to TSQL.
- Full-Text Search: Full-text search is now a fully integrated feature of a database. Wow that took forever. I still remember the 8 hour Microsoft support call back in 2002, that included my rant session to support about the need for 1. Logging, 2. Database integration.
- SP_DEPENDS: Identifies down to the columns of each table your stored procedure uses.
Things I am still waiting for:
- Row Level Security
- Something equivalent to the Oracle %ROWTYPE attribute in variable declarations as a datatype specifier.
Beating the Exam Jitters
Posted by Michael Ritacco in Career & Certification on February 3, 2009
How to Prepare for Certification Exams
- Most people find it essential to establish some special time for learning. Be sure to set it for when you learn best … in the morning, early evening, etc., and stick to your schedule.
- Use an effective method of study. Your own plan, if effective, is good. If yours didn’t work, you might try SQ3R method which involves 5 steps: survey the test objectives, generate questions before reading a section of the text, read a section, recite what you have read, and review the chapter.
- When you are just beginning to organize your study habits, don’t be too ambitious. Don’t try to crowd all your time for study in the day and leave no time for other things.
- Do not assume you can increase your capacity more by adding more hours of study. The idea is to use your time wisely. Ever notice the students who get the high grades are usually active in extra curricular activities? They can’t all be geniuses. They budget their time.
- Make a chart of your activities for the week. Block in study time.
- Space your learning periods during the week rather than concentrating them all at one time. You will be less fatigued and each time you will come with a new fresh approach.
- Do not study similar technologies, back to back. This tends to increase interference and makes the retention of material more difficult.
- Plan in advance for times–when more intensive study will be needed for exams.
- Follow the schedule after it has begun and do not let it “slide”. You will probably need revisions, but you must stick to it to succeed. Do not let yourself be distracted. Let everyone know that this is your study time and you will be free at a later time.
Emotional and Physical Preparation
- Your attitude toward the subject influences your effectiveness and/or ability to learn the subject. “If you think you can, you will”
- Poor or bad attitudes towards exams are usually due to the fact that:
A. The student is asked to perform a task which he/she has not practiced in real life. The questions are asked differently and he/she is afraid she/he will not be able to recognize the correct response.B. Much emphasis is placed upon the terms hard and difficult, when
thinking of exams. Who ever heard of-an easy exam? This puts you in the attitude of thinking “I can’t do it” with the resultant panic. - Panic is the greatest enemy of the test taker. You might also add “worry” to this statement. It is natural to have some tension, but panic is destructive. What causes it?
- There are four (4) real or supposed causes:
- A. Real incompetence. This is rather a common cause. The student sees a great deal to learn and he is afraid he knows nothing and really doesn’t know what’s important. He/she lose all perspective.
- Social sheep-following. Here the student follows the group. If the gang thinks it’s (panic) the thing to do, then the student follows suit. Seen usually in teenage groups.
- Seeds of doubt. Here the student listens to panic builders who are
always saying, “this test is horrible”. “No one ever passes that exam.”
The more the student thinks about it the greater the panic. - Students have also pointed out that the following will produce panic;
not knowing what the exam expects of the examine, trick questions,
lack of knowledge about vocabulary.
- How can you help yourself if you’re a panicker? Try these:
a. Believe what panic you have built up you can break down.
b. Don’t expect miraculous results but work steadily.
c. Be practical. You can’t just ‘will’ to not panic.
d. Use your planned study as insurance. It’s a means of strengthening your confidence. - Study wisely and do not allow poor study habits to rob you of rest, recreation or refreshments. You need all four in moderation.
- Get some testing software and use them. Give yourself the allotted time and check answers.
- The use of “pep pills”, or stimulants, is foolish. You pass the exam at the expense of physical health. The old saying that “once or twice can’t hurt you” is not true. It usually leads you to do it the 3rd or 4th time.
If you are rested, relaxed and ready, You will perform much better.
Taking Certification Tests
- Come prepared with pen, pencil, and paper.
- Arrive ahead of time. If you rush in, find your seat and begin, you are in no frame of mind to take the test. Now you must spend precious time catching your breath and settling down.
- Know the general nature of the test: Adaptive, Multiple choice,etc.
How much time do you have?
How much does each question count?
Can you leave out any questions?
Do you understand the directions?
Plan your time before you begin. Be strategic! - If, during the test, You find yourself panicking, take 10 slow, deep breaths. The preoccupation of taking them slowly will allow you a rest period.
- Answer the easy questions first. This way you will give yourself confidence and can tackle the hard ones.
- Don’t become upset by the apparent length or complexity of the test.
One of the advantages of this type of test is that the exam can ask
more questions about a variety of material and can, therefore, cover
more material. The length has nothing to do with the content. - Read and follow the directions carefully.
a. Many times the test will have a sample question as a guide.
b. Do as the exam asks: Circle, Underline, List. etc.
c. Watch the wording. - Be sure you understand the question. Reread or comeback later, if necessary.
- Don’t spend too much time on any one question. Mark It and check later. Sometimes a later question will “trigger” your memory. If not, you’ve only lost a few points.
- Do not think the every question is out to trick you. Questions are to test knowledge, not semantics.
- Use cunning. Watch for those give away words (Always, All, Every, Some, Usually).
- In answering matching questions, do the ones you know and cross
out the answers as you go along. You’ll save time by not having to look through all of the terms for the answer you select. - In multiple choice questions, mark out the wrong choices to eliminate.
Then read the statement through and see if you agree with your answer. - In completion of questions, watch the endings (a, an), length of line,
number of lines. These are clues to answers. - Read the whole question twice! Pay particular attention to questions that have an “all of the above” alternative. Some test takers stop reading the question if the first answer is right. If two of the answers are right, the third must be right, and “all of the above” is the correct answer.
- Mark clearly those questions you got stuck on. When you finish the test,run back through and spend extra time on these. Don’t change answers. This is the usual panic time and you’ll tend to want to read over the whole test. Don’t do that.
- When you have accomplished all this, end your test!!!!
The Secret is in the Thinking
Posted by Michael Ritacco in Career & Certification on January 4, 2009
Critical thinking is what is tested by all computer certifications across the country. Its proponents use the term to emphasize what they believe to be a superior and more relevant style of testing–one in which the focus of the exam is the testing of technical knowledge combined with reasoning and judgment skills.
SQL Server 2005 Data Types
Posted by Michael Ritacco in SQL Server on December 27, 2008
SQL Server 2005 has the following data types available:
- Exact Numerics
- BIGINT
- INT
- SMALLINT
- TINYINT
- BIT bit
- DECIMAL
- NUMERIC
- MONEY
- SMALLMONEY
- Approximate Numerics
- FLOAT
- REAL
- Date and Time
- DATETIME
- SMALLDATETIME
- Character
- CHAR
- VARCHAR
- TEXT
- Unicode Character
- NCHAR
- NVARCHAR
- NTEXT
- Binary
- BINARY
- VARBINARY
- IMAGE
- Other
- CURSOR
- SQL_VARIANT
- TABLE
- TIMESTAMP
- UNIQUEIDENTIFIER
- XML
Database Management 101
Posted by Michael Ritacco in Database Administration on December 27, 2008
Author: Khaled Kahlouni
Companies have a wealth of information systems as they continue to collect mountains of data on a daily basis. The need to house their information and have them readily available will always be there. Therefore, creating, monitoring, and maintaining the databases that house the data, pose higher challenges on the Information Technology department; in specific, the database administrators and their management. Even though now-a-days, we continue to see improvement in hardware design, operating systems stability, and database engines features; the need for database lifecycle management, best practices, and process standardization has not and will not change anytime soon.
Databases are the heart and soul of every systems/applications built or acquired within an organization. They are the center of all information, data movement, and communication of all systems. If the data is the most valuable asset of every company so is the database that houses it, making the database administration role the most critical job in IT. Therefore, effective database management is an essential task to ensure the availability, performance, recoverability, and security of such asset. Unfortunately, most companies do not realize the importance of the staff that administers their most valuable asset. They understand the need to have a staff but they always question their value and of course, their compensation. Thus, pressuring the Database Administrator staff to do nothing but, monitor the databases.
Database Administrators are highly talented individuals. They have to maintain their skills and keep up with new DBMS technology. In addition, they have to have a good understanding of all applications, data behavior, and hardware technology as they swiftly solve issues and problems with the databases they support – They are excellent problem solver individuals. So, their talent is beyond administration. But, due to the wrong perception by their management, their concentration is usually shifted to only monitoring and backing up the database, leaving several database administration roles un-played. Thus, forcing the database administrators to lose the essential skills to maintain and administer the most valuable company asset – Databases.
Every IT professional should try to answer the following questions as a health check:
- Have you ever tested recovering your production databases?
- How many times your database backup fails because of hardware or tape issues?
- How many times problems occur after a release or a migration?
- How many non-DBA users have more than read access to your production databases?
- How many times were you notified by your users of a problem before your staff knowledge of the problem?
- Do you keep current inventory of all databases?
- Do you know what your users query of your database?
- In case the data was lost, can your company survive?
This study will help your organization understand the necessary database administration roles. Most importantly, it will lay the proper roadmap for database administration.
The roadmap will guide your organization on how to be proactive rather than reactive. Catching and solving database problems before they occur proven to be more cost effective than reacting to the problem. For instance, using our roadmap will force you to monitor your space utilization. Thus, alerting of any space issues prior to your database running out of space.
Study’s Outline:
- General Assessment Process
- Understand Current Environments
- Database Management lifecycle Establishment
- Improvement Plan
- Measurement Process
- Production Maturity Process
Recent Comments