Tag: SQL

  • Normalization, Unions, Joins, and Locking in Microsoft SQL

    What is a normalized database?

    Database normalization is the process of organizing the fields and tables of a relational database to minimize redundancy. Normalization usually involves dividing large tables into smaller (and less redundant) tables and defining relationships between them.

    What is a SQL union/join?

    Unions combine rows. Joins combine columns. UNION combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union. By using JOINs, you can retrieve data from two or more tables based on logical relationships between the tables. Joins indicate how SQL should use data from one table to select the rows in another table.The UNION operation is different from using JOINs that combine columns from two tables.

    What is SQL locking?

    Locking prevents simultaneous changes to the data in Microsoft SQL Server. Locks are managed internally by the Microsoft SQL Server Compact Database Engine.

    How to open the Performance Monitor?

    On a Windows computer, click Start, click in the Search box, type perfmon.msc, and then press ENTER. In the navigation tree, expand Monitoring Tools, and then click Performance Monitor.

    What does the Performance Monitor do?

    The “Performance Monitor” under the “Microsoft SQL Server” entry under your Start Menu is the same “Performance Monitor” under the “Administrative Tools” entry under your Start Menu. They are the same programs. What is different is that when you bring up Performance Monitor from under the “Microsoft SQL Server” entry, is that it comes up already running several pre-configured SQL Server performance counters.

    Here are the key counters to watch on a regular basis:

    • Memory — Pages/Sec: To see how much paging my server is doing. This should be close to zero on a dedicated SQL Server. You will see spikes during backups and restores, but this is normal.
    • Network Interface — Bytes Total/sec: To see how much network activity is going on.
    • PhysicalDisk — % Disk Time — _Total: To see how busy all the disk drives are.
    • PhysicalDisk — Current Disk Queue Length — _Total: Also to see how busy the drives are.
    • System — % Total Processor Time: To see how busy all the CPUs are as a whole.
    • System — Processor Queue Length: Also see how busy the CPUs are.
    • SQLServer: General Statistics — User Connections: To see how many connections (and users) are using the server. Keep in mind that one connection does not equal one user. A single user can have more than one connection, and a single connection can have more than one user.
    • SQLServer: Access Methods — Page Splits/sec: Lets me know if page splits are an issue or not. If so, then that means I need either to increase the fill factor of my indexes, or to rebuild the indexes more often.
    • SQLServer: Buffer Manager — Buffer Cache Hit Ratio: To find out if I have enough memory in the server. Keep in mind that this ratio is based on the average of the buffer hit cache ratio since the SQL Server service was last restarted, and is not a reflection of the current buffer cache hit ratio.
    • SQLServer: Memory Manager — Target Server Memory (KB): To see how much memory SQL Server wants. If this is the same as the SQLServer: Memory Manager — Total Server Memory (KB) counter, then I know that SQL Server has all the memory that it wants.
    • SQLServer: Memory Manager — Total Server Memory (KB): To see how much memory SQL Server actual is using. If this is the same as SQLServer: Memory Manager — Target Server Memory (KB), then I know that SQL Server has all the memory that it wants. But if this is smaller, then SQL Server needs more available memory in order to run at its optimum performance.
  • Creating Custom Views in Microsoft SQL

    In part 3 I continue building on the table structure I learned about in part 2 of my training on How to Use Microsoft SQL by learning about creating custom views.

    Creating the Tables

    Microsoft SQL ViewTo learn how to setup a custom view in Microsoft SQL, we setup a second table so that we could join the data together. If you know Excel, then a good comparison is “Lists”, which are a separate table that can be referenced for a single cell. The second table shares a column name with the first table and it’s used to create a ‘list’ of sorts for the data in the first table.

    Once the second table has been created, we right-clicked on each table and selected “Edit Top 200 Rows” so we could populate it with some data. Microsoft SQL creates little red circles with white exclamation points when you’re entering data. This is meant to show you that the data isn’t saved. Clicking the red exclamation point in the top menu will save the data. ‘Grayed out’ data is read-only.

    Creating the Views

    Right-clicking on the database and choosing, “New View” will bring up a box that prompts you to choose a table (or tables) or click “Close”. Once you’ve selected your table(s), click close to view the View. A view is it’s own object in the database so it can be saved separately and referenced in other SQL code later on. Once a View is created, it can be edited.

    Along the top menu are different views that display the same data in different ways. One box brings up a visual view of the tables where you can draw lines between them. These lines are called “joins” and are simply visual representations of code, which can be viewed in a separate window. Unlike other programs, multiple views can be viewed at once. If you’ve ever used Access, you may be familiar with the screen where you can draw lines between tables. Microsoft SQL works similarly. Like in Access, the bold rows are the primary keys.

    Editing the Views

    Each row can be selected to appear or not appear in the view. This creates a corresponding “SELECT” statement in the SQL code. The “Alias” column adds a ‘nice name’ to the data to help you distinguish it later on. The “Output” column further distinguishes whether or not to display the data. “Sort Type” and “Sort Order” are used to sort the views and “Filter” is used to filter what data is displayed.

    Saving the View

    The View is its own object so it can be saved just like a table. To keep the object straight in your mind when coding, it’s best practice to append the View name with “vw” like this: vw[ViewName].

  • Microsoft SQL Primary Keys

    I’m in the process of learning SQL and in so doing have started learning Microsoft SQL using Microsoft’s SQL Management Studio. It’s a free program that’s used to open, manage, and edit Microsoft SQL databases stored on a server running Microsoft SQL (quick note: SQL is a database language used to store data in tables that can be accessed from programs or websites).

    2014-05-16 11_26_21-Connect to ServerWhen you first load the program (I’m running SQL Server 2005, which is not free), if you haven’t already opened a database, it will prompt you to connect to one. If or once connected to a database, it will appear in the left menu.

    The places you’ll be working in the most are under the “Database” folder: Tables, Views, and Programmability > Stored Procedures. For this post I will be focusing on Tables and specifically, creating Primary Keys.

    Table Best Practices

    To create a table, right-click on the Tables folder and select “New Table”. SQL will automatically append a “.dbo” to the front of the table name, but to keep things clear later on, add your own “tbl” or something similar to the beginning of a table name.

    Once you have created a table, create a new column to use as a primary key, then change the type to INT for integer. This will create a way to identify rows that are unique to the table, which allows all sorts of other functions to work correctly later on.

     

  • Self-Hosting Tips for WordPress

    Thinking about hosting your own WordPress domain on your own server? Here are a few things to consider.

    If you use our hosting you are using a linux-based server running WordPress on a SQL database. If you hosted it yourself you would still have to have a linux server with access to a SQL database unless you wanted to convert the site to flat files that could run on any type of server. WordPress is a content management system originally developed for blogging that allows a user to login and make changes to their site, add, or remove content. Flat files are the old way of displaying a web site, which requires manually editing a file, then uploading it via FTP.

    The domain is hosted and registered with 1and1, the largest hosting company in the world. The actual server is in a data center in Kansas City, Kansas. It gets regular maintenance, backups, and has redundant power and cooling systems. We are happy to facilitate the change of your domain, but you should know the requirements of the website first.

    My recommendation is for you or your new IT department to get full FTP and WordPress access to the current account as a first measure followed by signing up for their own hosting account with 1and1, BlueHost, or HostGator (which all support WordPress) before buying and setting up your own Linux server with access to a SQL database. There are other security concerns too to take in consideration when you invite the public into your private network. A capable IT technician should be able to build adequate ‘DMZ’s and firewalls, but the safest, easiest, and cheapest route is to have your own hosting account.

    What we offer is management of a hosted account. This includes keeping WordPresss’ software up to date, checking to make sure your plugins (WordPress programs like web forms) still work properly, checking for uptime, and sending monthly reports. This also allows us to make minor changes to text when you need it. We charge $125 a year for this service. Compare that to buying your own hosting account for $60 a year then having to do all of the maintenance and reporting yourself. If you have the trained manpower and it makes sense to go that route, we will provide the files to do so and unlock your domain when ready. Your IT department would be responsible for pulling the domain over, setting up the SQL database, installing WordPress, and installing the theme. Any assistance to these items would be billed at our $65 an hour rate.

    Just let us know what you decide to do. We are here to help.