I have a table called countries and I define the country_name column to be unique by creating a “Index/Key” of type “Unique Key” on SQL Server 2008 R2. But I have the following questions: will creating “Index/Key” of type “Unique Key” automatically create a non-clustered index on this column? if I change the type from … Read more
Is there any way to clear the Client Statistics table in Microsoft SQL Server Management Studio (2008 R2) besides create a new query? Answer Go to the “Query” menu then choose “Reset Client Statistics”. You can customise the toolbar to add this function if you require it frequently. AttributionSource : Link , Question Author : … Read more
nvarchar(max), varchar(max), or should I really decode the string and put it in an image or blob or something? The longer version : (with all the juicy details) I’ve got a .Net SOAP web service, which saves records in a SQL 2008 DB. The service is going to be extended to accept an image, which … Read more
There is a nice feature in ADO.NET that allows you to send multiple SQL statements to database in one roundtrip and receive results for all statements: var command = new SqlCommand(“SELECT count(*) FROM TableA; SELECT count(*) FROM TableB;”, connection); using(var reader = command.ExecuteReader()) { reader.Read(); resultA = reader.GetInt32(0); reader.NextResult(); reader.Read(); resultB = reader.GetInt32(0); } Is … Read more
I am trying to give all the permissions to a user in the User Mapping section of a database. But, I am encountering this error: “Cannot use special principal dbo” Server roles of the user: Answer This is happening because the user ‘sarin’ is the actual owner of the database “dbemployee” – as such, they … Read more
I was just tidying up some sql when I came across this query: SELECT jm.IMEI , jm.MaxSpeedKM , jm.MaxAccel , jm.MaxDeccel , jm.JourneyMaxLeft , jm.JourneyMaxRight , jm.DistanceKM , jm.IdleTimeSeconds , jm.WebUserJourneyId , jm.lifetime_odo_metres , jm.[Descriptor] FROM dbo.Reporting_WebUsers AS wu WITH (NOLOCK) INNER JOIN dbo.Reporting_JourneyMaster90 AS jm WITH (NOLOCK) ON wu.WebUsersId = jm.WebUsersId INNER JOIN dbo.Reporting_Journeys … Read more
How do I list files inside a folder in SQL Server without using the xp_cmdshell stored procedure? Answer You can use xp_dirtree It takes three parameters: Path of a Root Directory, Depth up to which you want to get files and folders and the last one is for showing folders only or both folders and … Read more
SELECT col1, col2, col3, EXEC GetAIntFromStoredProc(T.col1) AS col4 FROM Tbl AS T WHERE (col2 = @parm) How to write this SQL query in SQL Server 2008? Answer Thanks @twoleggedhorse. Here is the solution. First we created a function CREATE FUNCTION GetAIntFromStoredProc(@parm Nvarchar(50)) RETURNS INTEGER AS BEGIN DECLARE @id INTEGER set @id= (select TOP(1) id From … Read more
I’m building a report in Visual Studio 2008 with a lot of multivalue parameters and it’s working great, but I would like to have have the “(Select all)” option as the default value when the report is opened. Is there some kind of expression or SQL code I can use to make this happen? Or … Read more
I’m always discouraged from using one, but is there a circumstance when it’s the best approach? Answer It’s rare, but I have a few cases where it’s used. Typically in exception reports or ETL or other very peculiar situations where both sides have data you are trying to combine. The alternative is to use an … Read more
