Lesson 1

Creating a Database, the first course in the SQL Server 2016 Database Implementation Certification series. This series is designed to

1. Create and alter tables using T-SQL syntax (simple statements)
2. Create tables without using the built in tools; ALTER; DROP; ALTER COLUMN; CREATE
   Create and alter views (simple statements)
3. Create indexed views; create views without using the built in tools; CREATE, ALTER, DROP Design views
4. Ensure code non regression by keeping consistent signature for procedure, views and function (interfaces); security implications
   Create and modify constraints (simple statements)
5. Create constraints on tables; define constraints; unique constraints; default constraints; primary and foreign key constraints
   Create and alter DML triggers.
6. Inserted and deleted tables; nested triggers; types of triggers; update functions; handle multiple rows in a session; performance implications of triggers

To determine the version of SQL Server a database is using, follow these steps:

1. Connect to the SQL Server instance: Use SQL Server Management Studio (SSMS), or another tool that allows you to execute SQL queries, to connect to the instance hosting your database.
2. Execute the `SELECT @@VERSION` Query:
   • Open a new query window.
   • Type and execute the following SQL command: `SELECT @@VERSION;`
   • This command returns detailed information about the version of SQL Server, including the version number, build level, and the edition of SQL Server in use.
3. Alternative Query Using `SERVERPROPERTY`:
   • You can also use the `SERVERPROPERTY` function to get specific information about the SQL Server instance. Execute the following SQL command:
     

     SELECT 
     SERVERPROPERTY('ProductVersion') as ProductVersion, 
     SERVERPROPERTY ('ProductLevel') as ProductLevel, 
     SERVERPROPERTY ('Edition') as Edition;
     

   • This returns the version number, the update level (like RTM, SP1, etc.), and the edition of SQL Server.
4. Interpreting the Results:
   • The `ProductVersion` returns a string that represents the version of SQL Server in the format 'major.minor.build.revision'. For example, '15.0.2000.5' corresponds to SQL Server 2019.
   • The `ProductLevel` indicates the level of updates installed (RTM - Release to Manufacturing, SP - Service Pack).
   • The `Edition` provides information about the edition of SQL Server (e.g., Standard, Enterprise, Express).
5. Cross-Referencing with Official Documentation:
   • After obtaining the version information, you can cross-reference it with the official Microsoft documentation to understand the specific features and limitations of the version you are using.
6. Checking for Updates:
   • It’s important to check for the latest updates or service packs available for your SQL Server version, as they contain important fixes and performance improvements.

By following these steps, you can accurately determine the version of SQL Server being used by your database. This information is crucial for compatibility, troubleshooting, and ensuring that your database is up-to-date with the latest features and security updates.

As of January 17, 2024, there is no currently available exam specifically titled "Designing and Implementing Databases with Microsoft SQL Server 2019". Microsoft has retired several exams related to SQL Server 2019, and the most recently retired exam that closely aligns with that topic was
Exam 70-465: Designing Database Solutions for Microsoft SQL Server.
Here are some key points to consider:

1. Exam retirement: Microsoft regularly updates its certification exams to reflect the latest technologies and best practices. Exam 70-465 was retired on January 31, 2023, and is no longer available for scheduling.
2. Alternative options: While there isn't an exact replacement for Exam 70-465, there are other exams that cover SQL Server database design and implementation, focusing on more recent versions of SQL Server:
   
   • Exam DP-300: Administering Relational Databases on Microsoft Azure** covers database administration tasks, including designing and implementing databases on Azure SQL databases.
   • Exam DP-900: Microsoft Azure Data Fundamentals** provides a broad overview of data concepts and services on Azure, including database fundamentals.

Recommendations:

• Stay updated: Keep an eye on Microsoft's certification website for any announcements regarding new exams or changes to existing ones.
• Review exam objectives: Carefully examine the exam objectives of available exams to determine the best fit for your skills and career goals.
• Consider alternative learning paths: Explore Microsoft Learn for free online training resources and hands-on labs to enhance your SQL Server knowledge and skills.
• Consult Microsoft documentation: Refer to Microsoft's official documentation for comprehensive information on SQL Server 2019 and its features.

Additional tips:

• Hands-on experience: Gain practical experience with SQL Server 2019 through personal projects or professional work to reinforce your knowledge.
• Join communities: Engage with SQL Server communities and forums to connect with other professionals, share knowledge, and stay informed about industry trends.

After completing the course, you will be able to:

1. Describe the overall SQL Server 2022 architecture
2. Use the tools that come with SQL Server 2022
3. Define key relational concepts, such as relational integrity, data integrity, and prima, foreign, and unique keys
4. Create, modify, and delete databases
5. Create, modify, and delete tables
6. Use SQL Server constraints and indexes

As you probably know, you can interact with SQL Server in two ways: by issuing Transact-SQL commands, and by interacting with its GUI, or Graphical User Interface. This course uses Slideshow and Tooltips to explain the code syntax used in Transact-SQL commands, and simulations for practicing different elements of the SQL Server GUI. For additional information on these and other applets, see the Course Orientation .

Creating a Database is the first of three courses in the SQL Server 2019 Database Implementation Certification Series.
In the next lesson, you will learn about prerequisites to this course.