All posts tagged sql

If you missed a webinar, want to replay your favorites, or wish to share this training with colleagues, now is your chance! The webinar recordings are ready for viewing, learning, and sharing.
View Recordings
This event and session recordings are brought to you by the generous support of our presenting sponsors. View their sessions below:SQL Server on Linux – Don’t be afraid… this is the same SQL Server that you already know and love
Presented by Martin Wild, Systems Consultant, Quest

Introduction to Global Data Quality
Presented by Oscar Li, Product Channel Manager, Melissa
Getting the most from SQL Server – Deploying and Optimizing on Hyperconverged Infrastructure
Presented by Greg White, Solutions Marketing Principal, Enterprise Applications and Big Data, Nutanix

Paul Turley

Can’t We Just Get Along? Making SSRS, Power BI and Excel Play Well Together

Paul Turley

 10024 Hours of PASS (60 minutes)

Ginger Grant

Implementing Advanced Analytics with SQL Server 2017 and Python

Ginger Grant

 10024 Hours of PASS (60 minutes)

Kenneth Urena

Azure SQL VM – Implementing Basic AG in SQL 2016 STD

Kenneth Urena

 10024 Hours of PASS (60 minutes)

Scott Currie

Create and Load a Staging Environment from Scratch in an Hour with Biml

Scott Currie

 10024 Hours of PASS (60 minutes)

Oscar Li

Introduction to Global Data Quality

Oscar Li

 10024 Hours of PASS (60 minutes)

Gail Shaw

On Transactions and Atomic Operations

Gail Shaw

 10024 Hours of PASS (60 minutes)

Ben Miller

PowerShell for DBAs, What’s in it For Me?

Ben Miller

 10024 Hours of PASS (60 minutes)

Allan Hirt

The SQL Server 2017 Availability Story on Linux

Allan Hirt

 10024 Hours of PASS (60 minutes)

The original post appeared here:

http://sqlblog.com/blogs/adam_machanic/archive/2009/04/28/sqlclr-string-splitting-part-2-even-faster-even-more-scalable.aspx

Please visit the original website for more discussion, this is for reference purpose only.

 

using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(
FillRowMethodName = "FillRow_Multi",
TableDefinition = "item nvarchar(4000)"
)
]
public static IEnumerator SplitString_Multi(
[SqlFacet(MaxSize = -1)]
SqlChars Input,
[SqlFacet(MaxSize = 255)]
SqlChars Delimiter
)
{
return (
(Input.IsNull || Delimiter.IsNull) ?
new SplitStringMulti(new char[0], new char[0]) :
new SplitStringMulti(Input.Value, Delimiter.Value));
}

public static void FillRow_Multi(object obj, out SqlString item)
{
item = new SqlString((string)obj);
}

public class SplitStringMulti : IEnumerator
{
public SplitStringMulti(char[] TheString, char[] Delimiter)
{
theString = TheString;
stringLen = TheString.Length;
delimiter = Delimiter;
delimiterLen = (byte)(Delimiter.Length);
isSingleCharDelim = (delimiterLen == 1);

lastPos = 0;
nextPos = delimiterLen * -1;
}

#region IEnumerator Members

public object Current
{
get
{
return new string(theString, lastPos, nextPos - lastPos);
}
}

public bool MoveNext()
{
if (nextPos >= stringLen)
return false;
else
{
lastPos = nextPos + delimiterLen;

for (int i = lastPos; i < stringLen; i++)
{
bool matches = true;

//Optimize for single-character delimiters
if (isSingleCharDelim)
{
if (theString[i] != delimiter[0])
matches = false;
}
else
{
for (byte j = 0; j < delimiterLen; j++)
{
if (((i + j) >= stringLen) || (theString[i + j] != delimiter[j]))
{
matches = false;
break;
}
}
}

if (matches)
{
nextPos = i;

//Deal with consecutive delimiters
if ((nextPos - lastPos) > 0)
return true;
else
{
i += (delimiterLen-1);
lastPos += delimiterLen;
}
}
}

lastPos = nextPos + delimiterLen;
nextPos = stringLen;

if ((nextPos - lastPos) > 0)
return true;
else
return false;
}
}

public void Reset()
{
lastPos = 0;
nextPos = delimiterLen * -1;
}

#endregion

private int lastPos;
private int nextPos;

private readonly char[] theString;
private readonly char[] delimiter;
private readonly int stringLen;
private readonly byte delimiterLen;
private readonly bool isSingleCharDelim;
}
};

Translating SQL to LINQ can prove difficult for new and experienced C# developers. This post contains common SQL queries written in LINQ. I hope it’ll serve as a reference when writing LINQ queries. I’ll use a MS SQL database and Entity Framework for my examples. However, these examples can be extracted to other ORMs and databases. Also, consider reading why LINQ beats SQL to learn how to think in LINQ terms rather than translating SQL to LINQ.

Data model

We’ll use a simple data model that contains books and authors for our examples.

Books authors and articles data model

Entity Framework data context

SELECT

See the data model and Entity Framework data context to understand the setup for the following examples.

*

TOP

single column

multiple columns

Where

See the data model and Entity Framework data context to understand the setup for the following examples.

single condition

AND

OR

IN

BETWEEN

Inner join

See the data model and Entity Framework data context to understand the setup for the following examples.

LEFT/RIGHT OUTER JOIN

See the data model and Entity Framework data context to understand the setup for the following examples.

LEFT

RIGHT

FULL OUTER JOIN

See the data model and Entity Framework data context to understand the setup for the following examples.

CROSS JOIN

See the data model and Entity Framework data context to understand the setup for the following examples.

GROUP BY

See the data model and Entity Framework data context to understand the setup for the following examples.

single column

multiple columns

HAVING

See the data model and Entity Framework data context to understand the setup for the following examples.

DISTINCT

See the data model and Entity Framework data context to understand the setup for the following examples.

UNION

See the data model and Entity Framework data context to understand the setup for the following examples.

UNION ALL

UNION

ORDER BY

See the data model and Entity Framework data context to understand the setup for the following examples.

single column