I would EXPECT the first query to be quicker, mainly because you have an equivalence and an explicit JOIN. In my experience IN is a very slow operator, since SQL normally evaluates it as a series of WHERE clauses separated by “OR” (WHERE x=Y OR x=Z OR…). As with ALL THINGS SQL though, your mileage … Read more
Here is another method, which may be easier than installing plugins or external tools in some situations: Do a select [whatever you need]INTO temp.table_namefrom [… etc …]. Right-click on the database in the Object Explorer => Tasks => Generate Scripts Select temp.table_name in the “Choose Objects” screen, click Next. In the “Specify how scripts should … Read more
To delete all records from a table without deleting the table. DELETE FROM table_name use with care, there is no undo! To remove a table DROP TABLE table_name
Expand the SQL Server Agent node and right click the Jobs node in SQL Server Agent and select ‘New Job’ In the ‘New Job’ window enter the name of the job and a description on the ‘General’ tab. Select ‘Steps’ on the left hand side of the window and click ‘New’ at the bottom. In … Read more
You can declare like this CREATE PROCEDURE MyProcName @Parameter1 INT = 1, @Parameter2 VARCHAR (100) = ‘StringValue’, @Parameter3 VARCHAR (100) = NULL AS /* check for the NULL / default value (indicating nothing was passed */ if (@Parameter3 IS NULL) BEGIN /* whatever code you desire for a missing parameter*/ INSERT INTO …….. END /* … Read more
Your query is very close. You should be able to use the following which includes the subject in the final select list: select u.name, u.subject, u.marks from student s unpivot ( marks for subject in (Maths, Science, English) ) u; See SQL Fiddle with demo
Some uses for APPLY are… 1) Top N per group queries (can be more efficient for some cardinalities) SELECT pr.name, pa.name FROM sys.procedures pr OUTER APPLY (SELECT TOP 2 * FROM sys.parameters pa WHERE pa.object_id = pr.object_id ORDER BY pr.name) pa ORDER BY pr.name, pa.name 2) Calling a Table Valued Function for each row in … Read more
They do different things. Use INSERT when the table exists. Use SELECT INTO when it does not. Yes. INSERT with no table hints is normally logged. SELECT INTO is minimally logged assuming proper trace flags are set. In my experience SELECT INTO is most commonly used with intermediate data sets, like #temp tables, or to … Read more
“Lock Escalation” is how SQL handles locking for large updates. When SQL is going to change a lot of rows, it’s more efficient for the database engine to take fewer, larger locks (e.g. entire table) instead of locking many smaller things (e.g. row locks). But this can be problematic when you have a huge table, … Read more
Set column as unique in SQL Server from the GUI: They really make you run around the barn to do it with the GUI: Make sure your column does not violate the unique constraint before you begin. Open SQL Server Management Studio. Right click your Table, click “Design”. Right click the column you want to … Read more