Call sp_spaceused.

If the size of a table is needed, use it with a table name: sp_spaceused table_name.

We have a lot of separate files and want to put them together into one.

Step 1: Create a list of all filenames as XML file

<files>
<file>file1.xml</file>
<file>file2.xml</file>
<file>file3.xml</file>
</files>

Step 2: Create an XSL transform

<xsl:template match="//files">
<Root>
<xsl:for-each select="file">
<xsl:copy-of select="document(.)"/>
</xsl:for-each>
</Root>
</xsl:template>

Note the use of “copy-of” – it will copy all the nodes and attributes from the source XML.

We have “DISTINCT” to select unique entries, but what about duplicates? Here’s how:

SELECT email,
COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )

Instead of using long “select-case when-else” construction, we can use COALESCE function when we want to check if the value is NULL (this makes the code much shorter)

This is the long CASE WHEN construction:

CASE
WHEN (expression1 IS NULL) THEN 0
ELSE expression1

Here’s the use of COALESCE function

COALESCE(expression1,0)

More info

Code:

daysInMonth = Day(DateSerial(thisYear, thisMonth + 1, 0))

It adds 1 to the month and uses DateSerial to get a Date representing the 0th day of the following month. That is the last day of the month you entered. The Day function gives you the number of that day, which equals the number of days in the month.

Well, as I’m posting my tips here, I came across the problem with pasting programming code. Now I’m looking for a good online utility that would take code and HTML-escape it. It would be even better if it also did color-coding.

If I find something good, I’ll post a link here. For now, I’ll just be using a utility to escape all > and < entities. This is the first one I found in Google.

I needed to break a string like this “Hot Waffles,Fresh Fruits,Pastries,Bagels,Cereals,Muffins,Juices,Milk,Coffee” into separate XML elements. How to do it? Use a “tokenize” function!

<xsl:template name=”breakfast”>
<xsl:param name=”allItems”/>
<xsl:for-each select=”tokenize($allItems, ‘,’)”>
<item><xsl:value-of select=’.’/></item>
</xsl:for-each>
</xsl:template>

Result:

<item>Hot Waffles</item>
<item>Fresh Fruits</item>
<item>Pastries</item>
<item>Bagels</item>
<item>Cereals</item>
<item>Muffins</item>
<item>Juices</item>
<item>Milk</item>
<item>Coffee</item>

select top 10 * from table order by newid()

When there’s a large amount of data to be deleted, the performance of SQL suffers a lot. One way around it is to delete in batches, for example 10 000 rows at a time.

Instead of :
delete from yourtable where [condition]

i’d use:

— set rowcount to delete sets of 10 000 records (use a smaller subset size if you think it’s better)
set rowcount 10000
— start delete
delete from yourtable where
while @@rowcount > 0 — loop until there’s no record left to delete
begin
— after each delete there will be an implicit commit
delete from yourtable where
end

To save as Excel CSV file:

Response.ContentType=”application/csv”
Response.AddHeader “Content-Disposition”, “filename=myfile.csv”

To save as any type:

Response.ContentType=”application/x-msdownload”
Response.AddHeader “Content-Disposition”, “filename=sitemap.xml”