Thank you to NSU, all presenters, volunteers, participants and sponsors for making this year’s code camp a great experience. A special thank you to Dave Noderer who helped organize the event and allowed me to use the community table to speak with people about getting involved in Code for Fort Lauderdale. The speakers did a great job focusing on application rather than theory and helped me to walk away with useful information.
I had the opportunity to attend the below sessions. I have included the notes for each of them.
I hope everyone that attended enjoyed themselves, and I am looking forward to seeing everyone again next year.
Intro to SQL Server CE – Benefits & how to use
by Walter V. Williams, Jr.
Notable highlights regarding SQL Server CE 4.0
- Small footprint, less than 2 MB
- Easy to install(copy dll’s, no registering)
- Database management done within program (no need to hire DBA)
- Similar CRUD calls as in SQL Server express or full
- Full aggregate support ( I think joins too)
- Simple setup for development with Nuget repository
Limitations to keep in mind
- No stored procedures or triggers
- No role based security
- No direct upgrade path to sql server or full (i.e. you need to recreate structure and move the data yourself)
- Limited to 256 simultaneous connections
- Maximum size of 4 GB
- No support for datetime stamp(workaround using bigint to store ticks)
We don’t need roads: A developer’s look into sql server indexes
by Richie Rump
Stack Overflow is kind enough to do a data dump. Besides being an incredibly open treasure trove of information, it is a free large database to use for testing, indexes in this case. Richie was kind enough to provide a basic overview for those of us who aren’t DBA’s, experts or just a little rusty.
Starting with the baseics, we took a look at some execution plans and turned on IO statistics. You can use the free online statistics io parser to make the output more readable. The performance implications of scans and seeks became pretty clear, but it is important to understand the difference.
- Scans -> read the entire table
- Seeks -> use indexes to take shortcuts
These shortcuts are similar to the way we use alphabetical ordering in a phone book rather than flipping through each page while searching for an entry.
It turns out there are many different types of indexes.
- clustered -> tell how to physically store the data, only 1 (highlander) uniqueifier?
- nonclustered -> do not have to unique, maximum of 999 per table
- heaps -> stored by the order it comes in (default without clustered index)
- column store -> sort by column value rather than row
- xml -> make xml faster, but shouldn’t be there
- spatial -> for mapping stuff
- full-text -> text field, search inside of text
Tables are a metadata construct, the clustered index is the table. Included columns -> put columns into the leaf level of the index. This prevents bookmark lookups in SQL Server.
If a column is in the where clause use it in the index, if it is in the select use it in the included columns index.
The Fill Factor has a significant impact on performance. It determines how full a page in memory can be before a new one is created. It only applies to leaf pages. Each leaf page contains a reference to each of it’s entries. Pages are typically organized sequentially, which doesn’t work so hot for guids. The result is fragmentation, which creates empty space all over the place. Using sequential guids can help this issue, but it doesn’t solve the issue completely.
Indexes presents some disadvantages and limitations.
- Creates copies of the data(storage)
- Insert, Updates and deletes are slower
- Additional maintenance to keep indexes fast
- Cannot create index on nvarchar(max)
Big takeaway: Be a part of the community!
A DiBA guide to SQL Server Database Standards
by Diana Betancourt
South Florida SQL Server User Group (SFSSUG)
One of the initial questions asked was,
What are database standards?
Unfortunately the only standards that exist are for coding and not configuration. The solution is a marriage of Microsoft’s best practices and your own.
Best practice analyzers and risk assessments . Have fun with it, because it can take a lot out of you. Create a set of rules that don’t overburden development or deployment. Developers and DBA’s have to play nice. No issue should ever happen twice.
Question everything! Don’t assume that someone else’s advice will work for your situation or system. Prove that it will, and understand why. This course introduced me to The Enterprise Policy Management Framework. This seems like an invaluable tool for anyone that is trying to run a sizeable production environment, which is no small task.
The Enterprise Policy Management Framework is a reporting solution on the state of the enterprise against a desired state defined in a policy. Extend Policy-Based Management to all SQL Server instances in the enterprise. Centralize and report on the policy evaluation results.
clean = easier to read and maintain
- nodejs is multi-threaded
- Each page load is an application
- Inline code
- The DOM (Mixture of concerns and a tight coupling)
- Separate Your Concerns
- Every page load is an application
- Make it testable, and you will make it clean
Recommended Unit Test Frameworks
Recommended Test Runners