Filestreams are a great way to store files in SQL Server. SQL Server makes this super easy by managing the storage of the physical files on the file system somewhere. But what if you want to get access to these files or if you just want to know where they’re stored. SQL Server provides a function called PathName that you can call to get this path. You call this function like you would call an extension method in .Net. In the example below, we’ll assume that the column InvoiceFile is a Filestream datatype.
The client application port that connects to SQL Server is usually a random port between 1024 and 5000. This port on the client computer is what port SQL Server will use to send data back to the client with.
Firewall Considerations
If you are looking to setup a firewall to work with SQL Server ports, you will need to make the following settings:
Allow traffic from any port greater than 1024 to port 1433
Allow traffic from port 1433 to any port greater than 1024
Although some of these keyboard shortcuts may seem a little odd at first… once you start using them I think that you’ll love them and use them all the time.
Ctrl + R – Show/Hide the results pane
Ctrl + N – New query window
Ctrl + Shift + U – Switch current selection to uppercase
Ctrl + Shift + L – Switch current selection to lowercase
F5 – Execute the currently selected query (or entire query if nothing is selected)
Ctrl + Tab – Switch to the next open tab
Ctrl + Shift + Tab – Switch to the previous open tab
Ctrl + Space – Pops up IntelliSense complete if it is not already open
Ctrl + Shift + R – Refresh local IntelliSense cache
One way that SQL Server optimizes stored procedure execution time is by caching the execution plan. This functionality is built-in to SQL Server and is done every time that you create a stored procedure.
What Is An Execution Plan?
An execution plan in SQL Server is simply the way that SQL Server will go about getting the data for the client. When a query is executed in SQL Server, it will generate an execution plan. This execution plan is a set of steps that is the most efficient way to get this data from the database for the user.
How Do You Know If You Need To Rebuild An Execution Plan?
Okay, this is not very scientific. Usually what I’ve found in the past is that when a stored procedure runs fast one day… and the next day it is running slow… one cause might be that the execution plan needs to be rebuilt.
How To Rebuild An Execution Plan?
If you think that a stored procedure in your database has an execution plan that is not the most efficient, here are a couple easy ways to rebuild the execution plan. Keep in mind that the following queries do not actually rebuild the execution plan. They merely force the stored procedure to rebuild the execution plan next time that it is executed.
Rebuild For One Specific Stored Procedure
In the example below, CustOrderHist is the name of the stored procedure we want to rebuild the execution plan for.
1
EXECsp_recompile'CustOrderHist'
Rebuild All Execution Plans For All Stored Procedures On Your Server
NOTE: The WITH NO_INFOMSGS option will suppress a generic message that comes up after executing the FREEPROCCACHE command.
1
DBCCFREEPROCCACHEWITHNO_INFOMSGS
Why Does This Happen?
As data changes in your database, the data in the index will change as well. As the statistics for an index change, what used to be the fastest way to pull data may no longer be the fastest way.
Automatic Rebuild Of The Execution Plan
Of course SQL Server does not require you to always rebuild the execution plans manually. There are triggers in the database that will cause an automatic rebuild of the execution plan.
Alterations to the table, view, stored procedure, or indexes used by the stored procedure
Updating statistics on the table or index that the stored procedure uses
Dropping an index used by the stored procedure
A lot of inserts or deletes from a table (causing a large change in the keys)
Executing a stored procedure with the WITH RECOMPILE option
