For Developers: 2 Things to Consider with SQL Server
The next time you find yourself involved in the design and early phases of creation of an application that works against SQL Server, you’ll make things easier for yourself with a couple of items addressed as early on as possible.
– Data Types – I’ve seen more and more examples of people using nVarChar as a data type that hold information because they’re "not sure" what to use in the long run. Figuring it’ll hold just about anything they might need, the schema is created using these and then… forgotten.
Not a good plan, take the time to figure out your data elements, and the FLAVORS of those elements. In some cases there are different types that you can use that will optimize the storage and the way SQL Server works with your information. Dates, numbers, etc – each of these have several options. Figure out what you need *out* of your system, then figure out what the inputs will need to be to support it.
– Determine relationship requirements – what keys will you need and what foreign key relationships will be needed? Sure, you can build applications and views that make much of this possible without the whole relationship being a known entity.
The thing that’s really changing, however, are the tools that people are using to query your information and pull back reporting and knowledge. If your keys and relationships are defined correctly, these tools (like PowerPivot, other BI tools) can do their job much more efficiently and with less opportunity for missing a relationship.
– Start with the End in Mind – it came up a bit in the Data Types, but make sure you know what the end goal is. It’s like having a conversation with the boss where you just don’t get where they’re coming from complaining about your shoe laces. Find out what the real issue is – what is the application looking to solve?
With this information, you can make sure you’re building in support for the data elements to answer those questions. In so many cases, the application gets ready to deploy, or even is deployed, and then a user says something like "Great! Can I see my report about XYZ?" You find out you didn’t even know that’s what they were after all along… worse, you didn’t gather the "Y" element.
Don’t get surprised with what the end-users and stakeholders are really after. It’s so much easier to make it address these in the beginning (or be more transparent about the fact that you won’t be addressing it) than to retrofit and have to quickly engineer less optimal solutions to address their requirements.
What do you wish every DBA and developer started with, considered, or simply made sure they were doing with project kick-offs?
Shoot me an email (swynk@sswug.org) or comment below, let me know!