SQLServer 2005/2008/2008R2/2012/2017 TipsMicrosoft SQL Server is very easy to use right after the installation, compared with Oracle 11g. Updated 5/15/2020 Index
Where is SQLServerConfigurationManager?Search (using Search Bar) on the taskbar.Type SQLServerManager14.msc (14 for SQLServer 2017=14, 2012=11)You have a choice on the right pane. “Open file location” will open File Explorer.Find SQLServerManager14.msc (for SQL server 2017) and Pin to taskbar.Now you don’t search for it anymore. How can I get the max memory configuration on SQL server?SQL server keeps using more memory until exhaused and thus if you have SQL server and an application live on the samemachine, the application get choked by SQL server. SELECT name, value, [description] FROM sys.configurations where name like 'max server memory%' This will produce name value description max server memory (MB) 16384 Maximum size of server memory (MB) How can I get the version info on SQL Server?Here is how using SQL Server Manager and open new query window. use master select @@version go How can I install SQL server without windows update?SQL Server install as is requires Windows Update Service running. In our environment, this service is disabled by the IT. In this situation, you have to use the command line (as admin) to setup.
Open the command line console as admin and issue the following command.
Setup.exe /Action=Install /UpdateEnabled=False
How can I convert a named instance to default instance?Change the TCP/IP configuration using Configuration Manager. TCP DynamicPorts blank and TCP Port 1433. Seehttps://stackoverflow.com/questions/35026/sql-server-convert-a-named-instance-to-default-instance. How can I restore the database which was used?I tried to restore a database but the restore failed. This is due to the fact that the database has an open connection. Here is the T-SQL command use master; alter database (name of database) set offline with rollback immediate (after the restore) alter database (name of database) set online with rollback immediateThis is described in https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e96c985f-aea3-4bf8-b660-a71c1e3e612b/force-to-close-existing-connections-when-restoring-existing-database?forum=transactsql. How can I update the stored procedure?Here is the steps. https://msdn.microsoft.com/en-us/library/ms345356.aspx To modify a procedure in Management Studio In Object Explorer, connect to an instance of Database Engine and then expand that instance. Expand Databases, expand the database in which the procedure belongs, and then expand Programmability. Expand Stored Procedures, right-click the procedure to modify, and then click Modify. Modify the text of the stored procedure. To test the syntax, on the Query menu, click Parse. To save the modifications to the procedure definition, on the Query menu, click Execute. To save the updated procedure definition as a Transact-SQL script, on the File menu, click Save As. Accept the file name or replace it with a new name, and then click Save. How can I change the datetime to a particular datetime (year, month, day)?I needed to change the year, month, day to a particular one in DATETIME column. Here is how. Just add the difference where the database is $database and the datetime column is AIR_OPERATION and I want to set the year-month-day to July 5th of 2000: UPDATE [$database].[dbo].[AIR_OPERATION] SET [OPERATION_TIME] = DATEADD(dd,DATEDIFF(dd, [OPERATION_TIME],'2000-07-05'),[OPERATION_TIME]) GO How can I kick out the user of the database?I needed to restart SQL Server and thus need to kick out users using SQL server. Here is how. 1. Run SQL Server Manager 2. Right click on the server in Object Explorer and select 'Activity Monitor' 3. Expand Processes Group 4. Kill processes owned by the user by 'Kill Process'. How can I see who is using the database?T-SQL command to find out who is using the database is exec sp_who; go How can I add a user to SQL Server?It is really pain to add a new user for ASP.NET testing. CREATE LOGIN ASPUSER WITH PASSWORD = '(reasonable password)' GO -- now add user to allow connect USE ASPTEST; CREATE USER ASPUSER FOR LOGIN ASPUSERGO -- now add user to roll EXEC sp_addrolemember 'db_datareader', 'ASPUSER'EXEC sp_addrolemember 'db_datawriter', 'ASPUSER'EXEC sp_addrolemember 'db_accessadmin', 'ASPUSER'EXEC sp_addrolemember 'db_backupoperator', 'ASPUSER'EXEC sp_addrolemember 'db_ddladmin', 'ASPUSER'EXEC sp_addrolemember 'db_denydatareader', 'ASPUSER'EXEC sp_addrolemember 'db_denydatawriter', 'ASPUSER'EXEC sp_addrolemember 'db_owner', 'ASPUSER'EXEC sp_addrolemember 'db_securityadmin', 'ASPUSER'EXEC sp_addsrvrolemember 'ASPUSER','sysadmin' The last line was needed to see the database in Server Manager. How can I get the server login user?I was writing ASP.NET page to access SQL server and wanted to create special user.I wanted confirm that I created correctly. SELECT * FROM sys.server_principals How can I understand SQL server error?You can find more information generated by SQL server in http://www.codeproject.com/Articles/809083/SQL-server-error-details How can I create a database diagram?After creating a new database, I wanted to see the diagram for it. Unfortunately,SQL Server Management Studio gave an error message when I right-click DataBase Diagrams →New Database Diagram.  Here is the thing you have to do. Right-click on the database → Properties → Files.Owner is set to your login. Click "..." button and then Browse. You pick "NT Authority\System".  How can I create a local aliase for a remote server?We run daily unit tests on a server. However, when a unit test fails, we want to check onmy local machine. However, the unit test has the connection string pointing to the remoteserver. How can we test this unit test locally without modifying the connection string? Here is how: Create an aliase.
1. Program File → Microsoft SQL Server 2008 R2 → Configuration Tools → SQL Server Configuration Manager
2. Open SQL Native Client 10.0 Configuration.
3. Right click on Aliases and click on New Aliases.
4. Set Aliase Name pointing to the remote server, Port No = 1433, Protocol = TCP/IP,
and Server = localhost.
How can I drop many tables with a similar name?I wanted to eliminate many tables with names like 'RESULT_1', 'RESULT_2, ...Here is how. declare @cmd varchar(5000) -- cursor gathers the results of 'select' (many rows) declare cmds cursor for select 'drop table [' + name + ']' from sys.tables where name like 'RESULT_%' open cmds while 1=1 begin -- 'fetch' gets each row from cursor fetch cmds into @cmd if @@FETCH_STATUS != 0 break exec(@cmd) end close cmds deallocate cmds Give me the list of objects in sys.sysobjectsI needed a xtype description in sys.sysobjects. Here is the linkhttp://msdn.microsoft.com/en-us/library/ms177596.aspx. How can I shrink the database in size?I had the occasion of having a database bigger than 1 Gbytes. The db person told me that I can shrinkthe size. One example is that 2 Gbyte becomes 70 Mbytes. You are warned that shrinking sometimes slows thedata access. The details is described in http://www.karaszi.com/SQLServer/info_dont_shrink.asp. The command is DBCC SHRINKDATABASE andDBCC SHRINKFILE. Some people say that you never shrink! due to the increase in fragmentation.You use the following to find out what the fragmentation is:
SELECT [avg_fragmentation_in_percent]
FROM sys.dm_db_index_physical_stats (
DB_ID ('(DatabaseName)'), OBJECT_ID ('(TableName)'), 1, NULL, 'LIMITED');
GO
More on fragmentation/defragmentation, seehttp://www.sqlservercentral.com/articles/SHRINKFILE/71414/. Download it from the attachment at the bottom of this page. See (the page may be gone) http://blog.sqlauthority.com/2010/01/12/sql-server-fragmentation-detect-fragmentation-and-eliminate-fragmentation/. How can I drop constraint/restore constraint on the database?Sometimes you want to drop constraints on the database tables so that you can changethe content quickly. Of course, this may instroduce the consistency of the database and thusshould be used carefully. Here is how. Remove All Constraints EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all" Return All Constraints EXEC sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all" How can I create Geography report?SqlServer2008R2 can store geography information into table. The Sql Server Manager is fine forlooking at it but cannot save the spatial info into an image. In order to do this, you need touse Report Builder under SqlServer 2008 R2 Reporting Service. Here is the details.http://cm-bloggers.blogspot.com/2009/12/further-adventures-in-spatial-data-with.html. First of all, you have to set up Reporting Service. 1. Startup → Sql Server 2008 R2 → Configuration Tools → Reporting Service Configuration Manager. 2. Encryption Keys → Delete Encrypted Content → Delete 3. Service Account → Use another account. Account(Domain\user) and Password. This step will set up the Reporting Service available on the web server. Now bring up the Internet Explorer as administrator. Then put the address as http://localhost/reports.You get SQL Server Reporting Services Home page. Note that Report Builder in SQL Server 2008 R2 needs .NET Framework 3.5. If you get the message that you don't have .NET Framework 3.5 installed, then it is due to IE9's compatibility mode is not set (IE9 default behavior is to ignore .NET Framework 3.5). You can change this by hit Alt key to bring Menu. Then click Tools → Compatibility View Setting. Add "localhost" to the list. How can I reduce the memory usage?I was wondering why my physical memory usage is maxed out on my PC with 12 Gbyte memory installed. It turned out that SQLServer x64 maximum server memory is set to a ridiculous number. Open SQL Server Manager. Right-Click on the server on the left pane. Pick Properties. Select a page for Memory. Change "Maximum server memory (in MB) for something reasonable. List of error numbers and descriptions for SqlGeometryWhen I get SqlGeometry exception, I sometimes have no idea why. By having this table,you know what kind of errors are checked against your geometry or geography. Here is the list from http://social.msdn.microsoft.com/Forums/en-US/sqlspatial/thread/6b73e701-be81-424b-a052-29b5efa0d5e9.
Error Numbers and Descriptions-------------------------------------
24100 The spatial reference identifier (SRID) is not valid.
SRIDs must be between 0 and 999999.
24101 The distance parameter ({0}) for STBuffer is not valid.
Distances cannot be infinite or not a number (NaN).
24102 The point index n ({0}) passed to STPointN is less than 1.
This number must be greater than or equal to 1 and less than
or equal to the number of points returned by STNumPoints.
24103 The geometry index n ({0}) passed to STGeometryN is less than 1.
The number must be greaterthan or equal to 1 and should be less than or equal to
the number of instances returned by STNumGeometries.
24104 The ring index n ({0}) passed to STInteriorRingN is less than 1.
The number must be greaterthan or equal to 1 and should be less than or equal to
the number of rings returned by STNumInteriorRing.
24105 The geometryType argument to InstanceOf ('{0}') is not valid.
This argument must contain one of the following types:
Geometry, Point, LineString, Curve, Polygon, Surface, MultiPoint, MultiLineString,
MultiPolygon, MultiCurve, MultiSurface or GeometryCollection.
24106 The distance ({0}) passed to IsNear is not valid.
Distances must be positive numbers.24107The distance ({0}) passed to BufferWithTolerance
is not valid. Distances cannot be infiniteor not a number (NaN).
24108 The tolerance ({0}) passed to BufferWithTolerance is not valid.
Tolerances must be positivenumbers.
24109 The intersectionPatternMatrix argument to STRelate is not valid.
This argument must containexactly 9 characters, but the string provided has {0} characters.
24110 Character {0} ({1}) of the intersectionPatternMatrix argument to STRelate is not valid.
This argument must only contain the characters 0, 1, 2, T, F, and *.
24111 The well-known text (WKT) input is not valid.
24112 The well-known text (WKT) input is empty.
To input an empty instance, specify an emptyinstance of one of the following types:
Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon, or GeometryCollection.
24114 The label {0} in the input well-known text (WKT) is not valid.
Valid labels are POINT,LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, or GEOMETRYCOLLECTION.
24115 The well-known binary (WKB) input is not valid.
24117 The LineString input is not valid because it does not have enough distinct points.
A LineString must have at least two distinct points.
24118 The Polygon input is not valid because the exterior ring does not have enough points.
Each ring of a polygon must contain at least three distinct points.
24119 The Polygon input is not valid because the start and end points of the exterior ring are not the same.
Each ring of a polygon must have the same start and end points.
24120 The Polygon input is not valid because the interior ring number {0} does not have enough points.
Each ring of a polygon must contain at least three points.
24121 The Polygon input is not valid because the start and end points of the interior ring number {0} are not the same.
Each ring of a polygon must have the same start and end points.
24122 The MultiLineString input is not valid because the LineString number {0} does not have enough points.
Each LineString in a MultiLineString must have at least two points.
24123 The MultiPolygon input cannot be accepted because the contained Polygon number {0} is not valid: {1}
24124 The GeometryCollection input cannot be accepted because the contained geometry number {0} is not valid:{1}:
24125 The tolerance ({0}) passed to Reduce is not valid. Tolerances must be positive numbers.
24126 Point coordinates cannot be infinite or not a number (NaN).
24127 Using Reduce with the specified tolerance will yield an instance that is not valid.
Using a smaller tolerance may achieve a valid result.
24128 The Geography Markup Language (GML) input must have a single top-level tag.
24129 The given XML instance is not valid because its top-level tag was {0}.
The top-level element of the input Geographic Markup Language (GML) must be one of
Point, LineString, Polygon, MultiPoint, MultiGeometry, MultiCurve, or MultiSurface.
24130 The given XML instance contains attributes. Attributes in Geography Markup Language (GML)input are not permitted.
24131 The given pos element provides {0} coordinates. A pos element must contain exactly two coordinates.
24132 The posList element provided has {0} coordinates. The number of coordinates in a posListelement must be an even number.
24133 The linearRing input is not valid because there are not enough points in the input.
A linearRing must have at least 4 points, but this linearRing input only has {0}.
24141 A number is expected at position {0} of the input. The input has {1}.
24142 Expected "{0}" at position {1}. The input has "{2}".
24143 The posList element provided is empty.
24144 This operation cannot be completed because the instance is not valid.
Use MakeValid to convert the instance to a valid instance. Note that MakeValid may cause the points of a geometry
instance to shift slightly.
24145 The spatial index grid bounding box is incorrectly defined.
24146 The cells per object parameter to the spatial index must be between {1} and {2}.
24147 The grid density parameter to the spatial index is not valid.
24148 The distance ({0}) passed to BufferForDistanceQuery is not valid. Distances cannot be infinite or not a number (NaN).
24200 The specified input does not represent a valid geography instance.
24201 Latitude values must be between -90 and 90 degrees.
24202 Longitude values must be between -15069 and 15069 degrees.
24204 The spatial reference identifier (SRID) is not valid. The specified SRID must match one of the supported SRIDs
displayed in the sys.spatial_reference_systems catalog view.
24205 The specified input does not represent a valid geography instance because it exceeds a single hemisphere.
Each geography instance must fit inside a single hemisphere. A common reason for this error is that a polygon
has the wrong ring orientation.
24206 Cannot compute the distance between antipodal points.
24207 The specified buffer distance would cause the output to wrap around the globe.
24208 A reliable result could not be computed.
24209 Unexpected end of input. Check that the input data is complete and has not been truncated.
24300 Expected a call to {0}, but {1} was called.
24301 Expected a call to {0} or {1}, but {2} was called.
24302 No more calls expected, but {0} was called.
24303 The OpenGis{0}Type provided, {1}, is not valid.
24304 Nesting overflow. The call to {0} would result in {1} levels of nesting. Only {2} levels are allowed.
24305 The Polygon input is not valid because the ring does not have enough distinct points.
Each ring of a polygon must contain at least three distinct points.
24306 The Polygon input is not valid because the start and end points of the ring are not the same.
Each ring of a polygon must have the same start and end points.
How can I reinitialize IDENT field?If you have IDENT field, then it will be incremented automatically.Sometimes I need to reinitialize. Here is how:
USE [STUDY_SWISS]
GO
DBCC CHECKIDENT ('dbo.JOB_RUN_OPTIONS', RESEED, 0 )
GO
where STUDY_SWISS is the database name and JOB_RUN_OPTIONS is the table in it.How can I read a file content into a database?I wanted to get values from a file into the table. Here is how. CREATE TABLE #Numbers (N int) BULK INSERT #Numbers FROM 'd:\tmp\Numbers.txt' SELECT * From #Numbers2 BULK INSERT works only with a temp table or a real table, not with a table variable. When you have more columns, you add with (FIELDTERMINATOR = ',') for CSV file. How can I copy stored procedures from another database?I was creating a new database, but I needed stored procedures from another database.Here is how. 1. Right Click the other database and select Tasks then Generate Scripts. 2. Now you have Generate and Publish Script dialog. 3. Select Choose Object in the left Pane. 4. Select Select specific database objests in the right Pane. 5. Select Stored Procedures in the treeview of the right Pane. 6. Once you have saved it, you just load File and change the database name. How can I insert Geography for a column in a tableI needed to insert geography into one of the column in a table. I did not want to useSqlCommand.AddWithValue(). The trick is to use geography.ToString() functionality by quoting the argument. This sg becomes a quoted WKT representation, i.e. 'MULTILINE(...)' etc. which is again converted into SqlGeography when inserted.
Under SQL Server Manager Script
UPDATE dbo.RECEPTOR
SET LOCATION = 'POINT (-122.373495049439 37.4852716153839)'
WHERE RECEPTOR_ID = 13ADO.NET
SqlGeography sg;
...
string insertCommand = StringFormat("INSERT INTO {0} VALUES('{1}')", tableName, sg);
// create SqlCommand and then ExecuteNonQuery() show in the next item
Note that the first case SqlGeography is created from WKT POINT string.How can I insert NULL for a column in table?I was creating a string for SqlCommand in ADO.NET where one of the column may have a null value.No need to use SqlCommand.AddWithValue(). Here is the way:
int? valCanBeNull;
int value; ... string valCanBeNullText = (valCanBeNull.HasValue) ? valCamBeNull.Value.ToString() : "NULL";
string insertCommand = String.Format("INSERT INTO {0} VALUES ({1}, {2})", tableName, valCanBeNullText, value);
SqlConnection cnn = new SqlConnection(connectionString);
...
using (SqlCommand cmd = new SqlCommand(insertCommand, cnn))
{ cmd.ExecuteNonQuery(); }
How can I compare stringI wanted to write "where" statement with some fuzzy sense for string.Here is the info http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=69527. WHERE string1 LIKE string2 Note that "string2" can include the wildcard operators: % (0 or more characters) _ any single character (i.e. will NOT match an empty string) [ABC0123] a single character, one of the set "ABC0123" [0-9] a single digit [^0-9] a single character which is not a digit WHERE [BADA_ID] LIKE 'B77%' will match 'B772', 'B773', 'B77L' and 'B77W' (different Boeing aircraft type). Further Regular Expressions are not currently supported - so you can't do:[0-9]+[A-Za-z]+for one or more digits followed by one or more lettersYou can also use an ESCAPE character if you need to escape one of the reserved characters: WHERE MyColumn LIKE '%\_FOO\_%' ESCAPE '\' How can I delete the sharename for FILESTREAM?I had a terrible time updating SQLServer2008 to SQLServer2008R2, ended up removing the instance MSSQLSERVER andreinstalling it. Meanwhile I had FILESTREAM enabled before. After the installation, I get an error saying ShareName is not valid or is already in use. Try a different sharename or delete the existing share when I try to enable filestream. It turned out that the registry location at "HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLSERVER\Filestream" contained the default MSSQLSERVER as ShareName. What I did was to rename this to MSSQLSERVER2.Then I was able to enable FILESTREAM for MSSQLSERVER instanace again. Then I re-edited the ShareName to be MSSQLSERVER. Now everything is fine. I was able to install the Server2008 Sample codes which uses FILESTREAM. What are the differences among stored procedures, views, and user defined functions?(still developing)Views cannot have DECLARE statement.
User Defined Functin (UDF) cannot access temporary variables
(those with #(name)) inside a function (i.e. temp Table cannot be used but Table variable is OK).
Stored Procedure RETURN only single numeric scalar assignment. How can I enable CLR integration?As default, clr integration is turned off. You type the following inSQL Server Manager Query Editor and then Execute! ( 1 for enable, 0 for disable): sp_configure 'clr enabled', 1 GO reconfigure GO Give me an example of UDF returning Table.The following is an example of User Defined Function which returns a geography table from Table called Boundary_Points.
The original table consists of three columns, Boundary_Points_ID, LONGITUDE, and LATTITUDE of four rows of corners of a rectangle.
The method is using CURSOR to retrieve each row and cast them to create string Longitude and Lattitude to form WKT LINESTRING.
Using this UDF is like: SELECT * FROM BoundaryGeometry().
CREATE FUNCTION dbo.BoundaryGeometry()
RETURNS @Boundary TABLE (Boundary Geography NOT NULL)
AS
BEGIN DECLARE @Item varchar(100)
DECLARE @Polygon nvarchar(1024)
DECLARE C CURSOR FAST_FORWARD FOR SELECT cast(LONGITUDE as varchar(20)) + ' ' +
cast(LATTITUDE as varchar(20))
FROM dbo.BOUNDARY_POINTS
ORDER BY BOUNDARY_POINTS_ID
DECLARE @Item0 varchar(100)
OPEN C -- get the first point
FETCH C INTO @Item -- save this for closing the polygon
SET @Item0 = @Item -- cannot use POLYGON, since it paints inside
SET @Polygon = 'LINESTRING(' WHILE @@FETCH_STATUS = 0
BEGIN
SET @Polygon = @Polygon + @Item + ', '
FETCH NEXT FROM C INTO @Item
END SET @Polygon = @Polygon + @Item0 + ')'
CLOSE C DEALLOCATE C -- finished getting polygon
INSERT INTO @Boundary
SELECT Geography::Parse(@Polygon)
RETURN END
How can I enable FILESTREAM for T-SQL?I downloaded a sample code using FILSTREAM. When I try, it generated an error message"FILESTREAM feature is disabled". Here is how. Sql Server Configuration Manager → SQL Services → SQL Server (MSSQLSERVER). Double click and you get this dialog. How can I rename the table?I wanted to rename the table. Here is how. USE INM_JFK_SMALL GO EXEC sp_rename 'SUMMARY_NOISE', 'SUMMARY_NOISE_TEST' GOwhere the first argument for sp_rename is the original table name and the second argument for the new table name. How can I get the modification date of a table?I wanted to get the creation date and the modification date of a table.One way is to use the SQL Script and another way is to use LINQ query (VS2008). SELECT create_date, modify_date FROM sys.objects where name = '(tablename)' GO In order to use LINQ query, you have to create .dbml file. In order to do this for sysobjects,you have to open Server Explorer and select Views (not Tables) and open System View. You will find sysobjects which you can drop in .dbml. Once you drop it, then you can use LINQ query as follows:
using (STUDYDataContext dc = new STUDYDataContext(studyConnectionString))
{
// get modification date
var query = (from item in dc.sysobjects
where item.name == "SUMMARY_EMISSION"
select new { creation_date = item.crdate, modification_date = item.refdate}).SingleOrDefault();
DateTime cdate = query.creation_date;
DateTime mdate = query.modification_date;
}
How can I change geography column value?I wanted change the geography column which contains Lat, Lon, Height value in SQL Server Management Studio. Because it is in binary format, you have to use thescript to change. Here is the way: UPDATE [STUDY_JFK].[dbo].[RECEPTOR] SET [LOCATION] = 'POINT(-73.939 40.49)' WHERE [RECEPTOR_ID] = 2 GO SELECT [LOCATION], [LOCATION].Lat as Lat, [LOCATION].Long as Lon, [LOCATION].Z as Z FROM [STUDY_JFK].[dbo].[RECEPTOR] GO Note that the order is (Long, Lat, Z) in POINT. Since we omit Z value, Z will be NULL. How can I use Column Set to accommodate more than 1024 columns?SQL Server has the limit of 1024 columns. SQL Server 2008 added "Column Set" so that you can have up to 30000 "columns". The reason why I put quotes is that it is a fake column. The row size limit is still there (8091 bytes). You have one column which has the XML data with many "columns" (here is the reference to "Column Set"). Unfortunately I tried to google to find out how to create and use "column set" in vain. After many trials and errors, this SQL script gives you what it actually does:
CREATE TABLE t (ID int SPARSE, Age int SPARSE, Name varchar(50) SPARSE, cs xml column_set FOR ALL_SPARSE_COLUMNS)
GO
INSERT t(cs) VALUES ('<ID>3</ID><Name>Tosa</Name>')
GO
INSERT t (ID, Name) VALUES (4, 'Sky')
GO
INSERT t (Age, Name) VALUES (21, 'Walker')
GOSELECT * FROM t
GO
What you get is in Server Management Studio
cs 1 <ID>3</ID><Name>Tosa</Name>
2 <ID>4</ID><Name>Sky</Name>
3 <Age>21</Age><Name>Walker</Name>
Note that the missing values are ignored. The above example is just three "columns". The restriction is that you can add "SPARSE" elements as many as 30000 with constraint of 8019 bytes in total. More on column_set see http://blogs.msdn.com/b/sreekarm/archive/2009/01/08/sparse-columns-in-sql-server-2008.aspx. How can I see the serialized value in the table?When the data table contains the serialized value, you would see the alphabet soup.Here is the way. If the column name is [RESULT] for the serialized result, you use cast([RESULT] as XML) as Somenamewhere 'Somename' appears as the column name in place for RESULT. How can I find out the date touched the table?I needed to find out when the table is updated in a database. Here is the sql script thanks to Pinal Dave.
select OBJECT_NAME(object_id) as TableName, last_user_update,*
from sys.dm_db_index_usage_stats
where database_id = DB_ID('STUDY_TOSA_TINY') and object_id=OBJECT_ID('SUMMARY_EMISSION')
go
How can I check the existence of a table?The following is from ASP.FAQ. Note that xtype = 'u' is to look for user table.
IF EXISTS (SELECT 1 FROM sysobjects
WHERE xtype='u' AND name='tablename')
SELECT 'tablename exists.'
ELSE
SELECT 'tablename does not exist.'
ADO.NET code
private bool CheckTable(string tableName, string connectionStudy)
{
string commandTxt = "select COUNT(*) from sysobjects where xtype = 'u' and name = '" + tableName + "'";
// xtype = 'u' means "user table"
using (SqlConnection cn = new SqlConnection(connectionStudy))
{
cn.Open();
SqlCommand command = new SqlCommand(commandTxt, cn);
try
{
int num = (int)command.ExecuteScalar();
if (num == 1)
return true;
else
return false;
}
catch (Exception ex)
{
Trace.WriteLine("Look for STUDY table failed\n" + ex.ToString());
return false;
}
}
}
How can I copy a table in the database?I needed to preserve the table before the run so that I can compare the previous run with the new run. Here is the way for SQL Server 1. Create a sql script as follows and name it as ScriptCopyTable.sql: USE $(db) SELECT * INTO [dbo].[RESULTS_OLD] FROM [dbo].[RESULTS] GO ALTER TABLE [dbo].[RESULTS_OLD] ADD CONSTRAINT [PK_RESULTS_OLD] PRIMARY KEY CLUSTERED ([RESULTS_ID]) ON [PRIMARY] GO 2. Execute using sqlcmd as follows: sqlcmd -v db="STUDY" -i ScriptCopyTable.sql -bwhere I want to copy [RESULTS] table to [RESULTS_OLD] in the database STUDY and [RESULTS_ID] column as a primary key. Note that constraints are not copied. How can I save results with headers?I wanted to save the query result as a CSV file under Server Management Studio. As default, it won't save the header (column headers) in a CSV file. It turned out that it is hidden in the Option. SQL Server Management Studio → Tools → Options... Check Query Results → SQL Server → Results to Grid Check "Include column headers when copying or saving the results". After press OK, you have to close and re-open Server Management Studio (important!). How can I add database diagram support?When I restored the database to my server, I wanted to have the database diagram. Under SQL Server Management Studio, I clicked Databases → (DatabaseName) → Database Diagrams. What I get is the following messagebox.  What is not clear is that which login I have to set. It turned out that you do Why do I have to use (hostname)\SQLEXPRESS for SQLExpress?I was wondering why I have to add the instance name SQLEXPRESS for SQLExpress only install on a PC (no problem for SQLServer install). It turned out that SQLExpress does not become the default instance. From http://msdn.microsoft.com/en-us/library/ms165614(SQL.90).aspx, In SQL Server Express, a named instance is always used by default. You specify an instance name when you install SQL Server Express, or the default named instance of SQLExpress is used. If the default named instance is used, you reference the instance as computername\SQLExpress. In order to fix this, you have to do a trick described as follows: install SQL Server Express in a named instance using an instance name of MSSQLSERVER. This forces Setup to install SQL Server Express as the default unnamed instance. How can I find the SQL Server Names on a local machine?There are at least three ways to get the SQL server names on the network.
Method 1:
using System.Data.Common;
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.SqlClient");
DataTable dt1 = factory.CreateDataSourceEnumerator().GetDataSources();
// includes non-local servers
Method 2:
using System.Data.Sql;
DataTable dt2 = SqlDataSourceEnumerator.Instance.GetDataSources();
// includes non-local servers
Method 3:
// add reference to Microsoft.SqlServer.Smo
using System.Data.Sql;
DataTable dt3 = SmoApplication.EnumAvailableSqlServers(true);
// 'false' includes non-local servers
All of these methods returned ServerName's fine (Environment.MachineName). However, none of them returned instance names (returned values are empty). However, SQL Server Installer installs a named instance of MSSQLSERVER for Server2008 and SQLEXPRESS for SQL Server 2008 Express as default. Furthermore, ServerName is not enough to access the database. In my case DataSource required "(hostname)\SQLExpress". So far the only way to get instance names on the local machine is to use the registry. I have not found a way for non-local machines. Here is the way to get the instance names on the local machine returned.
public static string [] GetLocalInstanceNames()
{
// Look for @"HKEY_LOCAL_MACINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL"
RegistryKey root = Registry.LocalMachine;
RegistryKey key0 = root.OpenSubKey("SOFTWARE\\Microsoft\\Microsoft SQL Server");
if (key0==null)
{
MessageBox.Show("MS SQL Server not installed");
return null;
}
RegistryKey key1 = key0.OpenSubKey("Instance Names\\SQL");
return key1.GetValueNames(); // gives the instance names
}
How to avoid Installation Glitches?I spent five days to recover from my mistakes, trusting the installer. 1. Remove SQL Server 2005 Express from Visual Studio 2005 Uninstall. (Don't use the SQL Server 2005 uninstaller.) 2. Make sure that you create sa account with password.Microsoft SQL server installer (Developer version or Enterprise version) won't install SQL Server Manager app (and others) if you have SQL Server Express installed. Period. This is stupid in that I was trying to install the full version. Another bad thing is that the install directory of SQL Server Express uses the same directory as the non-express version. Thus it became a total confusion on the uninstaller when I realized that the installer did not install SQL Server Manager. I ended up to install Vista from scratch. Why can't I use the database after install?On Vista, Microsoft is requiring any user of SQL to have the SQL server login. The service pack install tries to do this but unfortunately when it tries to do it, the server is not running (as usual not tested by MS). Here is how to create a new login. Open Microsoft SQL Server Management Studio. (Note that without sa, you cannot connect to the server as I said in 2.) Then click on Security. You get this   When you right click on Login, then you get "New Login" option. Make sure that you use the syntax "(MachineName)\username". How can I find the connection string to use for SQL server?You need connection string to initialize SqlConnection object. Unfortunately, the examples given in a book usually does not work. The best way is to use Server Explorer to let it create a string and use it. You can test the connection and you are sure that it can be connected. Create a C# window application. Get Server Explore from View → Server Explore. Right Click on Data Connections. Click Add Connection .... Ignore Provider Tab (we used this tab for ODBC connection). Under Connection Tab, open the combo-edit box in 1. Select or enter a server name. Pick the one you want.   Then 3. Select the database on the server. Push Test Connection. If it is OK, then the setup for Data Connection is done.   Now it created (in my case) UDON\UDONSQL.biblio.dbo connection. If you right-click on this one and drag it onto the form, it will create sqlConnection1 object. If you look at Form1.cs code, you will find the connection string. // // sqlConnection1 // this.sqlConnection1.ConnectionString = "workstation id=UDON;packet size=4096;integrated security=SSPI;data source=\"UDON\\U" + "DONSQL\";persist security info=False;initial catalog=biblio"; Interestingly, it split the long string. The important thing is that this string is verified to work for SqlConnection. How can I delete an entire database from SQLServer?It is the easiest if you you SQL Management Studio. I needed a quick way to delete entire rows.
1. Pick Databases -> Open the database you want -> Expand Tables->dbo.LeftIris
2. Right Click on dbo.LeftIris
3. Pick "Script Table As ..." ->
Delete To DELETE FROM [Retica].[dbo].[LeftIris]
WHERE
4. Remove WHERE < > and
DELETE FROM [Retica].[dbo].[LeftIris]
5. Click "Execute" in the toolbar.
You get
Messages (39994 row(s) affected) Query executed successfully
How can I backup a database in SQLServer?Run SQL Server Management Studio 1. Right Click on the database named "Retica" 2. Select Task->Backup 3. Options->backup to a new media 4. Name it (Retica) 5. Verify backup 6. General 7. Add and name the backup file1. Task "Restore"2. From Device3. backup media "file" Which function to get values from DataReader?Here is the list thanks to http://www.functionx.com/adonet/Lesson08.htm.
Reading varbinary needs work. First you use int length = GetBytes(index, 0, nullptr, 0, 0);to retrieve the length and then allocate Array of length to retrive the entire data: GetBytes(index, 0, Array, 0, length); How can I create a LINQ class from a SQL Server Database table?I have a table in SQL Server 2008. The question is: how I can create a class for this table in C# so that I can run query using LINQ? As usual, VS2008 makes it easy. Here are the steps. 1. Add new item. Pick LINQ to SQL Classes. The default name is DataClasses1.dbml. Updated 8/25/2010 |
Database >
