Search Results

"Shrinking" the log is actually "clearing" the log. The log is basically a list of operations that have been performed, and is used as a reference for rollbacks, restores, checkpoints, etc. The spac …

It's unlikely it's related to the query plans. I've only every seen very complicated queries take more than 1 second for the plan to get created, much less 4 seconds as your describe. It sounds like …

You want to use the management views: sys.foreign_keys and sys.foreign_key_columns The latter one has column and object IDs for both sides of the constraint, so you can do a query like: SELECT OB …

You can use CROSS APPLY for this: SELECT ComputedColumn INTO Table2 FROM Table1 CROSS APPLY (SELECT SUBSTRING(Column1,1,3)) CxA(ComputedColumn) WHERE ComputedColumn !="dec"

I may be misunderstanding your requirements, but how about: Make an archive table that you update nightly (as you describe). You could potentially partition this on your date field. Alter your view …

There's no way this can be solved with a single index unless you have a FK Constraint on tab1.col1 which references tab2.col2 which means all the rows in tab1 meet the condition and the optimizer can …

Some things to try: Check your indexes Are all the JOIN key fields indexed? If you use this view a lot, I would go so far as to add a filtered index for the criteria in the view. For instance... …

A few points: 1 - This is indeed a bad idea. 2 - Your syntax should be updated. Using implicit JOINs is generally bad since it can lead to a cartesian product if you aren't careful. 3 - Have you l …

In my experience, the best way to do this is to: Create the EMPTY table Create the clustered index (matching your source table) INSERT using TABLOCK hint for minimal logging Details are included …

Those are page reads, mind you. That is relevant. Possibilities: Improper or insufficient indexing. Are any of the filtered fields indexed? How wide are the indexes (see below)? Poor page densit …

I think a separate table would be better to improve page density and reduce fragmentation, especially if you don't always populate that field. A data page holds around 8000 bytes You have some rows …

Just JOIN on a subquery: SELECT * FROM MyTable T INNER JOIN (SELECT OrderID, MAX(ProductID) as ProductID FROM MyTable GROUP BY OrderID) x ON x.orderid = t.orderid AND x.pr …

You could greatly simplify this by adding a few fields to your existing tables. Add: ChangeDate - smalldatetime that indicates when a record was added Active - BIT ChangeBy - user who added the rec …

The order of your index keys is the issue. You can create a partition aligned index on K1 to resolve this pretty easily. Right now your index on K0, K1 can't be used directly to find the max value q …

Without sample data I need to guess, but you want aggregations. For Days: SELECT CAST(OrderDate as DATE), as 'Date' COUNT(*) as 'Orders', SUM(OrderPaidAmt) as 'TotalAmpunt' FROM Order …