I work with an enterprise application and have picked up some tips for DB design All tables should have the following fields that helps in audit trail - LastChangedBy, LastChanged, LastChangedPage All your stored procedures that have dynamic SQL should have the @bDebug parameter. By default it's set to 0. If it's set to 1, print out t...
When you perform a left join in TSQL (MSSQL SERVER) is there any guarantee which row will return with your query if there are multiple rows on the right? I'm trying to use this to exploit an ordering on the right table. so Select ColA, ColB, ColC from T Left Outer Join (Select ColA, ColB, ColC from T--CLARIFIED, this is a sel...
I am trying to list last a website's statistics. I listed Last 30 days with; CONVERT(VARCHAR(10), S.DATEENTERED, 101) BETWEEN CONVERT(VARCHAR(10), GETDATE()-30, 101) AND CONVERT(VARCHAR(10), GETDATE(), 101) and this month with; RIGHT(CONVERT(VARCHAR(10), S.DATEENTERED, 103), 7) = RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) but ...
SELECT * FROM myTable WHERE field1 LIKE 'match0' AND myfunc(t1.hardwareConfig) LIKE 'match1' Here is my question, the matching of field1 is fast and quick, but myfunc takes forever to return and I want to make sure that if field1 doesn't match that it doesn't even attempt to do myfunc. Will SQL just know this or can I make it...
Hello everyone, I am studying snapshot isolation level of SQL Server 2008 from the below link. My confusion is, http://msdn.microsoft.com/en-us/library/ms173763.aspx It is mentioned "Data modifications made by other transactions after the start of the current transaction are not visible to statements executing in the current transact...
Hi to all, Please help in solving the following query, ( f.CURS_AMT, c.C_NAME, s.SID FROM COURSE_FEE_DTL f JOIN COURSE_MASTER c ON f.C_CODE = c.C_CODE JOIN STUDEDNT_MASTER s ON s. C_NAME = c.C_NAME ) When the sp is executed error arises as 'too many arguments specified' ...
Hello everyone, I am interested in and new to database type project in VSTS2008. I am especially interested in how this new type of project could benefit my development work (previous I always develop all SQL stuff inside SQL Server Management Studio), and whether this type of project could facilitate deployment of database. Appreciate...
Does performance of a database (SQL Server 2005) decrease if I shrink it? What exactly happen to the mdf and ldf files when shrink is applied (Internals???) ...
I have the table of following structure: UserID StartedOn EndedOn 1 2009-7-12T14:01 2009-7-12T15:01 2 2009-7-12T14:30 2009-7-12T14:45 3 2009-7-12T14:47 2009-7-12T15:30 4 2009-7-12T13:01 2009-7-12T17:01 5 2009-7-12T14:15 2009-7-12T18:01 6 2009-7-12T11:01 2009-7-12T19...
I want to track IP addresses visited my website. I want to know what time and what page they visit. I store ip address in VISITORIP, date entered in DATEENTERED and page URL in HTTPADDRESS columns. I want to group them by dates. My outcome should be like: TIME PAGE 7/12/2009 3:16:27 PM ?Section=products&SubSection=...
HiIs it possible to write SQL query that returns table rows in random order every time the query run? ...
Lets say you have various objects of arbitrary type that you would like to store in a key+value type of table. Key could for example be an int, string or guid. What would the value be? String, Binary or something else? And how would you store and load the objects? I would think some sort of serialization, but what kind? I have one so...
I would like to copy (publish for MsAccess users) some tables from sqlserver 2K to an MsAccessDatabase. Is this possible in TSQL? If not, as an alternative, is it possible to call DTS-packages from the command-line? UPDATE : tx to the answer of John Sansom I explored DTSrun, and it works just fine. This update for the syntax : dtsr...
I have a SQL Server database (2008) within which I have some CLR DLLs that SQL uses for various things. Are these DLLs mirrored when I use SQL Mirroring? Our DBA says not, but I find this crazy and cannot find much info on it online. Is it possible to mirror the CLR DLLs? ...
Is there any utility availble to count the total lines of user created Stored Procedure, Function, Views in a Database? ...
I have a table which have a single field. and it have a values like (3,7,9,11,7,11) Now I want a query which will pick the value that occurred least number of times and if there is a tie with minimum occurrences then use the smallest number In this case the answer will be 3. ...
I am trying to reduce fragmentation in all of the indexes for a database running on SQL Server 2005. Currently I am trying to use ALTER INDEX in conjunction with sp_MSforeachtable, to apply it to all of the indexes for all of the tables: sp_MSforeachtable "ALTER INDEX ALL ON ? REBUILD;" But for some reason this doesn’t always seem to ...
Does anyone have or know of a SQL script that will generate test data for a given table? Ideally it will look at the schema of the table and create row(s) with test data based on the datatype for each column. If this doesn't exist, would anyone else find it useful? If so I'll pull my finger out and write one. ...
I'm trying to call a stored procedure from my .NET code which has one output paramater. Its all standard ADO.NET stuff using SqlCommand, SqlParameter and so on. But I'm getting the error below even though my transaction level is READ COMMITTED. You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation le...
The following code snippet on SQL server 2005 fails on the ampersand '&': select cast('<name>Spolsky & Atwood</name>' as xml) Does anyone know a workaround? Longer explanation, I need to update some data in an XML column, and I'm using a search & replace type hack by casting the XML value to a varchar, doing the replace and updating ...