Most of these are really basic. Some are my take on established ideas and standards, and may be controversial. Agree or disagree? All feedback is welcome! (well ... mostly the "I agree, you're a genius" feedback is welcome .
Read more →
Here’s my advice of the day: Try your best to store all of the data relating to your database in the tables.
Sounds pretty obvious, I know, but you might be surprised just how much data in your application is not in your tables, where it belongs, but rather nestled away in your code.
Read more →
My Top 10 Things I Hate About SQL post has been an interesting social experiment, to say the least. I knew I was taking a risk by not clearly stating that it was intended as humor, but I thought it would be funny to just post it to see what kind of responses came back.
Read more →
One aspect of the versatile SELECT statement that seems to confuse many people is the GROUP BY clause. It is very important to group your rows in the proper place. Always push GROUP BY aggregations as far into your nested SELECT statements as possible – if you have a situation in which you are grouping by long lists of columns that are not part of primary keys, you are probably have not structured your query correctly.
Read more →
Congratulations to our good friend Alex Papadimoulis over at the asp.net weblogs on his Microsoft MVP award. Well-earned!
Read more →
These are actual, unedited SQLTeam post subjects that you also might find in a dating forum:
Checking for Date Conflict - How to identify those tell-tale signs that your date isn't going well; bringing your laptop along doesn't usually help Problem with relationships - Usually this is the result of not being able to identify date conflicts Pickup Rate based on effective date - Be sure that all payment rates and the definition of “effective” are agreed upon before picking up your date.
Read more →
Here's kind of a funny/scary thread over at the DailyWTF:
http://www.thedailywtf.com/forums/49490/ShowPost.aspx
see also:
Discussing VB and/or MS Access in a Programming Forum Top 10 Things I Hate About SQL Server SQLTeam Dating Advice !
Read more →
Many SQL books and tutorials recommend that you “avoid cross joins” or “beware of Cartesian products” when writing your SELECT statements, which occur when you don't express joins between your tables.
Read more →
By an anonymous SQL user (a guest blogger) 1. SQL is too complicated! When I have multiple values in my columns, like "JAN,FEB,MAR" or “15,84,22” , SQL Server just doesn't get it.
Read more →
OK, so let's sum up my recent flury of crosstab / pivoting posts the past week or so:
Here's a dynamic way to do crosstabs in SQL Server But here's an alternate way to do this at the presentation layer in .
Read more →
(For information about using this code click here.)
<%@ Language=VBScript %>
<%OptionExplicit%>
<html>
<head>
<metaname=vs_targetSchemacontent="http://schemas.microsoft.com/intellisense/ie5">
<metaname="GENERATOR"Content="Microsoft Visual Studio .NET 7.1">
</head>
<body>
<%
dim con 'ADODB.Connection
dim com 'ADODB.
Read more →
As promised in my last post, here is some performance testing to help you determine the performance benefits (if any) of performing your crosstabs at the presentation or code layer, as opposed to forcing SQL Server to do this.
Read more →
create view PivotTestasselect o.customerID, c.CompanyName, p.productName, sum(od.quantity) as Qty from orders o inner join [order details] od on o.orderID = od.orderID inner join Products p on od.
Read more →
usingSystem;
usingSystem.Data.SqlClient;
usingSystem.Data;
namespaceCSharpConsole
{
class CrossTabTesting
{
[STAThread]
static void Main(string[] args)
{
SqlConnection conn;
string s;
int i;
conn = new SqlConnection( "Server=(local);Database=Northwind;uid=xx;pwd=xx");
conn.Open();
Console.WriteLine("Beginning performance test for crosstab techniques.
Read more →
create procedure CrossTab2 (@SQL varchar(1000),
@PivotCol varchar(100),
@Summaries varchar(100),
@GroupBy varchar(100),
@OtherFields varchar(100) = Null,
@Debug bit = 0)
AS
set nocount on
set ansi_warnings off
Read more →
In my last post, I spoke briefly about how I felt that in general crosstabbing data is something that a presentation layer should do and not the database. Consider the result of a crosstab operation -- the columns returned will vary depending on the data.
Read more →
privatevoid Sample()
{// call this from a form ....
SqlConnection conn;
SqlCommand com;
DataGrid dg = new DataGrid();
dg.Parent = this;
dg.Dock = DockStyle.Fill;
String SQL =
Read more →
public static DataTable Pivot(IDataReader dataValues, string keyColumn, string pivotNameColumn, string pivotValueColumn)
{
DataTable tmp = new DataTable();
DataRow r;
string LastKey = "//dummy//";
int i, pValIndex, pNameIndex;
string s;
bool FirstRow = true;
Read more →
First off, before going any further make sure you have read the hall of fame SQLTeam article by Rob Volk on generating crosstab results using a flexible, dynamic stored procedure that has been viewed over 100,000 times!
Read more →
Commonly here at the SQLTeam forums, users seeking assistance explain in long detail their situation and mention how they have millions of rows of data to deal with or dozens of tables and how complicated it all is.
Read more →