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
Indexes can play an important role in query optimization and searching the results speedily from tables. The most important step is to select which columns are to be indexed. There are two major places where we can consider indexing: columns referenced in the WHERE clause and columns used in JOIN clauses. In short, such columns … Read more
If you delete the DB file, it still stays registered with SqlLocalDB. Sometimes it fixes it to delete the DB. You can do this from the command line. Open the “Developer Command Propmpt for VisualStudio” under your start/programs menu. Run the following commands: sqllocaldb.exe stop v11.0 sqllocaldb.exe delete v11.0
UPDATE R SET R.status=”0″ FROM dbo.ProductReviews AS R INNER JOIN dbo.products AS P ON R.pid = P.id WHERE R.id = ‘17190’ AND P.shopkeeper=”89137″;
When dealing with deleting data from tables which have foreign key relationships – which is basically the case with any properly designed database – we can disable all the constraints, delete all the data and then re-enable constraints — disable all constraints EXEC sp_MSForEachTable “ALTER TABLE ? NOCHECK CONSTRAINT all” — delete data in all … Read more
They’re used in different places. group by modifies the entire query, like: select customerId, count(*) as orderCount from Orders group by customerId But partition by just works on a window function, like row_number: select row_number() over (partition by customerId order by orderId) as OrderNumberForThisCustomer from Orders A group by normally reduces the number of rows … Read more
Martin Fowler wrote my favorite article on the subject, http://martinfowler.com/articles/evodb.html. I choose not to put schema dumps in under version control as alumb and others suggest because I want an easy way to upgrade my production database. For a web application where I’ll have a single production database instance, I use two techniques: Database Upgrade … Read more
In Management Studio, you can put a number after a GO end-of-batch marker to cause the batch to be repeated that number of times: PRINT ‘X’ GO 10 Will print ‘X’ 10 times. This can save you from tedious copy/pasting when doing repetitive stuff.
I found an easy solution Step1. connect to DB with an admin user using PLSQL or sqldeveloper or any other query interface Step2. run the script bellow; in the S.SQL_TEXT column, you will see the executed queries SELECT S.LAST_ACTIVE_TIME, S.MODULE, S.SQL_FULLTEXT, S.SQL_PROFILE, S.EXECUTIONS, S.LAST_LOAD_TIME, S.PARSING_USER_ID, S.SERVICE FROM SYS.V_$SQL S, SYS.ALL_USERS U WHERE S.PARSING_USER_ID=U.USER_ID AND UPPER(U.USERNAME) … Read more
Well, firstly you’ve omitted the ‘+’ from your string. This way of doing things is far from ideal, but you can do DECLARE @SQL varchar(250) SELECT @SQL = ‘SELECT * FROM ‘ + QuoteName(@Alias) Exec(@SQL) I’d strongly suggest rethinking how you do this, however. Generating Dynamic SQL often leads to SQL Injection vulnerabilities as well … Read more