Category: sql

Tips for Sql query performance

The following are a list of good practices I follow when querying large sets of data. SELECT * Avoid selecting all columns from a table. Queries should explicitly list each column it requires Changing column definitions on the underlying tables can have an adverse effect on dependencies such as views, name clashes etc Avoid reading and returning unnecessary data Can… Read more →

Generate POCO’s from Sql Server

DECLARE tableCursor CURSOR LOCAL FOR SELECT name, ‘Foundation’, name + ‘Dto’ FROM sys.tables WHERE 1 = 1 OPEN tableCursor DECLARE @tableName NVARCHAR(MAX), @schemaName NVARCHAR(MAX), @className NVARCHAR(MAX) FETCH NEXT FROM tableCursor INTO @tableName, @schemaName, @className WHILE @@FETCH_STATUS = 0 BEGIN DECLARE tableColumns CURSOR LOCAL FOR SELECT cols.name, cols.system_type_id, cols.is_nullable FROM sys.columns cols JOIN sys.tables tbl ON cols.object_id = tbl.object_id WHERE tbl.name… Read more →

Sql Server Shrink Log File

Easy with Simple Recovery DBCC SHRINKFILE (“<FileName>_Log”, 1); GO otherwise http://social.technet.microsoft.com/Forums/sqlserver/en-US/0a7a7fc5-d30e-4841-8ed1-84676b575e55/sql-server-2012-how-to-shrink-the-database-transaction-log USE AdventureWorks2008R2; GO — Truncate the log by changing the database recovery model to SIMPLE. ALTER DATABASE AdventureWorks2008R2 SET RECOVERY SIMPLE; GO — Shrink the truncated log file to 1 MB. DBCC SHRINKFILE (AdventureWorks2008R2_Log, 1); GO — Reset the database recovery model. ALTER DATABASE AdventureWorks2008R2 SET RECOVERY FULL; GO Read more →

How to run code from sql server in c#

How to run code from sql server in c# SqlCommand cmd = new SqlCommand(“select Developer from Stackoverflow where UserID=’786′ and Developer=’Sufiyan'”, con); if (con.State == ConnectionState.Closed) { con.Open(); } SqlDataReader reader = cmd.ExecuteReader(); if (reader.Read()) { string codeSnippet = reader[“CodeSnippet”].ToString(); dynamic script = CSScript.LoadCode(@” using System; using System.Windows.Forms; using System.Collections.Generic; using System.Data; using System.Windows.Forms; using System.Data.SqlClient; public class RunFromSqlServer {… Read more →