After staring at a blank IDE for a while, I decided what I reallywanted to do was create an app that would give me n number of prime numbers in a list.
Rules for optimization:
- Don't optimize.
- Optimize later.
I decided that the best way to go for this project would be to determine the next prime number from a given integer. That way, I could generate lists and other such nonsense. I settled on the following sequence:
public int nextPrime(int lastNo)
int currentNo = lastNo;
bool isNotPrime = true;
isNotPrime = false;
for (int i = 2; i < currentNo; i++)
if (currentNo % i == 0)
isNotPrime = true;
The premise of the function is that we want to find the next prime number, not whether the input number itself is prime.
- We create a copy of the input number (currentNo).
- We create a conditional check (is the number not prime?) and set to true. This is so we can pass into the first conditional.
- While the conditional is true (the number is not prime)
- Set the conditional to false. We are setting the assumption that the number is indeed a prime.
- increment the currentNovalue by 1.
- Create a for-loop to go through all of the numbers between 2 and the currentNominus 1.
- If the modulus of currentNodivided by the increment value (i) equals 0, then the number cannot be prime, so we set the test flag as true and break from the loop. Otherwise, we continue iterating through the set range of numbers until we exhaust it, or we manage to change the flag.
- If the test flag is true, re-run the sequence until it is not.
- Return the first value that results in the for-loop not changing the test flag to true.
- What is SQL?
SQL is an acronym for Structured Query Language.
- What is Normalization, and explain the different levels.
Normalization is a method for reducing redundancy in a database. The idea is that data is grouped together in such a manner to enhance a given performance goal.
1NF- Removes any duplicated attributes.
2NF - Should be 1NF and each non-key should be dependent on the primary key.
3NF - Should be 2NF and all non-key attributes that are not dependent on the primary key should be removed.
- What is Denormalization and when should it be applied?
Denormalization is the reverse of normalization. It increases query performance by reducing the number of joins by consolidating data.
- What is the difference between an Heap table and a Clustered Table?
HEAP table: A table in which the data is not stored in any particular order. There's no clustered index.
CLUSTERED Table: A table with a predefined clustered index on at least one column defining the storing order of the rows within the data pages, based on a clustered index key.
- How many clustered indexes can be created on a table?
One only. This because a table can be sorted in the table itself only once.
- What is the difference between a clustered and an non-clustered index?
A table can have multiple non-clustered indexes, but only one clustered index.
- What are the database relationship types?
One-To-One - For each instance in the first entity there is one and only one matching instance in the second entity, and vice versa.|
One-To-Many - For each instance in the first entity, there can be more than one matching instance in the second entity. The second entity will have one and only one matching instance in in the first entity.
Many-To_Many - For each instance in the first entity, there can be one or more matching instances in the second entity, and vice versa.
- What is the difference between a Primary and a Unique Key?
A Primary Key denotes a default clustered index, and does not allow nulls.
A Unique Key denotes a non-clustered index, and allows one null.
- What is a Query Plan?
A query plan is a physical breakdown of the code passed to the SQL Server optimizer.
- What are Candidate, Alternate and Composite Keys?
Candidate Key - a key that can uniquely identify a row in a table
Alternate Key - If the table has more than one candidate keys and one becomes a primary key, the rest become alternate keys.
Composite Key - More than one key to uniquely define a table row.
- What is a Transaction?
A Transaction is a work wrapper that creates a reversible work instance as long as the transaction has not been committed. Transactions conform to the ACID principle.
- What is ACID?
ACID is the acronym for Atomicy, Consistency, Isolation and Durability:
Atomicy: each transaction is to be 'all or nothing'. If one part of the transaction fails, the entire transaction fails.
Consistency: Any transaction will bring the database from one valid state to another. Any data written to the database must be valid according to the defined rules and structures of that database.
Isolation: Ensure that the concurrent execution of transactions would be the same as if they were entered serially. Transactions are independent of each other.
Durability: Once a transaction has been committed, it will remain committed, even after a system failure, environmental / hardware crashes, or errors.
- What are the primary steps in data modeling?
Logical - Planning, Analysis and Design
Physical - Design,Implementation and Maintenance
- What is a cursor?
A cursor is a mechanism that allows the developer to go throw a data result set row by row.
- What is a SPID?
A SPID is a Service Process ID. It is simply a SQL Server 'session' created anytime an application connects to SQL Server.
- What are the different types of cursors?
Static cursors: populates the result set at the time of cursor creation and the query result is cached. Can move forward and backward (scrollable). Most memory intensive of the cursor types.
No UPDATE, INSERT, or DELETE operations are reflected in a static cursor (unless the cursor is closed and reopened).
SQL Server static cursors are always read-only.
Dynamic Cursors: Allows the developer to see updates, deletes and inserts while the cursor is open. So the cursor will be sensitive to data changes.
Forward-only Cursors:The fastest of all the cursors. It does not allow for backward scrolling like static and dynamic cursors will.
- What is a SQL Server Deadlock?
A deadlock is when 2 SPIDs have data locked, and neither process can release their locks until one of the SPIDs releases.
- Why should you avoid using cursors in general?
A Cursor impacts the performance of the SQL Server since it uses the SQL Server instances' memory, reduce concurrency, decrease network bandwidth and lock resources.
- What is an execution plan, and how do you use it?
An execution plan is the graphical road map of the SQL Server process. It gives the developer an opportunity to analyze the performance characteristics of the query at hand.
- What is NOLOCK?
NOLOCK allows SQL to read from data tables that would normally be blocked by another query or process. It can improve performance, but can result in dirty reads.
This is only applicable with SELECT statements, as CRUD processes will result in an error.
- What is a 'Dirty Read'?
A dirty read is a query that has run against a table where an active transaction is set. This means that the data returned in the query could be 'out of date' from the process holding the transaction open.
- What is a Transaction Isolation Level?
This is how SQL Server controls the locking and row versioning of Transactions.
READ COMMITTED: Stipulates that statements cannot read data that has been modified but not committed by other transactions.
READ UNCOMMITTED: Stipulates that statements can read rows that have been modified, even if the row hasn't been committed by other transactions.
REPEATABLE READ: Specifies that statements cannot read data that has been modified but not yet committed by other transactions, and no other transactions can modify data that has been read by the current transaction until the current transaction completes.
SNAPSHOT: Specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction.
SERIALIZABLE: Statements cannot read uncommitted data, no other transactions can modify the data until the current transaction completes, and other transactions cannot insert new rows with key values within the range of keys managed by the current transaction.
- What are the different types of locks available in SQL Server?
Shared: used for operations that don't change data (like SELECT statements)
Update: used when updating resources
Exclusive: Used for CRUD processes
Schema: Used when an operation dependent on the schema is a table is executing.
Bulk Update: Used when bulk copying data into a table and the TABLOCK hint is specified.
Intent (shared, update, exclusive): Used to establish a lock hierarchy.
Key Range (shared, insert, exclusive): Protects a defined range of rows read by a query when using the SERIALIZABLE transaction isolation level.
- What is Lock Isolation?
It's the process of of converting may fine-grained locks into fewer, coarse-grained locks.
- Define Index Depth, Density and Sensitivity.
Index Depth: the number of levels from the index root node to the leaf nodes. If the depth is too deep, there will be performance degradation.
Index Density: the measure if the lack of uniqueness of the data in a table. A dense column is one that has a high number of duplicates.
Index selectivity: A measure of how many rows scanned compared to the total number of rows. High selectivity means there is a small number of rows scanned when related to total number of rows.
- What is the difference between an OLAP and a OLTP database?
OLAP- Online Analytic Processing: Databases are used for transactional systems. Most of the submitted queries are for data modification.
OLTP- Online Transaction Processing: Databases used for data warehousing systems. Most queries are data retrievals that filter, group, aggregate and join larger datasets very quickly.
- Name some tools / methods for monitoring SQL performance.
Third Party Applications
- How do you collect IO and time statistics for queries?
SET STATISTICS IO on: provides a textual count of table reads.
SET STATISTICS TIME on: provides compile and execution times.
- What is MVC?
MVC is an acronym for the Model, View Controller framework:
Model - The business object layer of the application
View - the front-end, or customer facing layer of the application
Controller - the business logic layer of the application
- What are the prime advantage of using MVC?
Since the view is separate from the model and controller, it's easier to create unique view that utilize the same Models and Controllers. It is also easier to update the View without changing the Model or Controller.
Separation of Concerns - As MVC denotes separate disciplines, it is easier to delegate coding responsibilities to different teams.
Testability - MVC is better suited to TDD than ASP.NET web form development
- What is the MVC application Life cycle?
CREATING THE REQUEST OBJECT
1. Fill Route - MVC requests are mapped to Route Tables that specify which controller and action to invoke.
2. Fetch Route - Depending on the URL sent, the "UlrRoutingModule" searches the route table to create the "RouteDate" object, which contains the details of which controller and action to invoke.
3. Request Context Created - The "RouteData" object is used to create the "RequestContext" object
4. Controller instance Created - The request object is sent to "MvcHandler" instance to create the Controler class instance. Once the Controller class object is created, the 'Execute" method of the Controller is called.
CREATE THE RESPONSE OBJECT
5. Execute the method defined in step 4.
6. Build the View for the requesting platform.
7. Send the resulting View data to the client.
- What are the different return types of a Controller action method?
ViewResult (view) - Returns a web page
PartialViewResult(PartialView) - Returns a partial view that is to be displayed in a different view.
RedirectToRouteResult(RedirectToAction, RedirectToRoute) - Used to redirect to another action method
ContentResult(Content) - Returns an HTTP content type (ie: text/plain) as a result
JsonResult(json) - Returns a json result set
FileResult(File) - Returns a binary file data response
EmptyResult - Returns noting (void)
- What are Filters in MVC?
FIlters are qualifiers that can be used in MVC to set pre/post behaviors on a Controller's action methods:
Action Filters - Used to implement logic that's executed before and after a Controller action executes.
Authorization Filters - Used to implement authentication and authorization for controller actions.
Result Filters - These contain logic that is executed before and after a View result is executed.
Exception Filters - Used to handle any errors raised by either Controller actions or Controller action results.
- What are the Action Filters in MVC?
Output Cache - caches the output of a controller action for a set amount of time
Handler Error - handles errors raised when a Controller Action executes.
Authorize - enables the developer to restrict access to a given user or role
- What is Routing in MVC?
Routing is the mechanism that processes the incoming URL for an MVC web site. It is generally thought of as more descriptive and able to provide a more detailed response.
- What is the difference between TempData, ViewData and ViewBag?
TempData - Used to pass data from the current request to the next request. Generally used to store 'one-time' messages such as error and validation messages. Requires typecasting for complex types.
ViewData - Used to pass data from the Controller to the View. Requires typecasting for complex types.
ViewBag - Used to pass data from the Controller to the View. This is a dynamic property, so it does not require typecasting for complex types.
- What is ETL?
ETL is an acronym for Extraction, Transformation and Loading. It the process of converting and loading data from one given source to another.
- What are the steps that define ETL?
Defining the source (setting the source connection to the source)
Defining the target (setting the target connection to its target source)
Creating the mapping (defining and setting the business logic)
Create the session (defining the mapping instructions)
Create the work flow (setting the instruction sets)
- What is a 3-tier data warehouse?
Here, the data is thought of as a 3 tiered system. The 1st layer is considered the source layer.
The 2nd layer is the integration layer, where 1st layer transformations are stored.
The 3rd layer is the dimension layer - this is where the presentation layer is accessed.
- What is a snapshot?
A snapshot is a read-only copy of the master data.
- What is a materialized View?
A materialized view is simply an aggregate table, or a database view object.
- What is round-robin processing?
Round robin means that we are processing more than one source or thread. Instead of reading or processing in a linear fashion, we have divided the source data into n units and, in turn, pull one element from each source.
Round robin also means that data is distributed evenly to n number of partitions.
- What is hash partitioning?
Hash partitioning means that we create a hash key for each row in a source data set and process the data by the generated hash key.
Hash partitioning also means that data is distributed evenly via a generated hash key for each data element.
A hash key is essentially a small data value meant to represent a larger piece of data.
- What is an Operational Data Store?
An Operational Data Store (ODS) is a repository between the staging area and the data warehouse.
- What is the difference between a Full Load, an Incremental Load and an Initial Load?
Initial Load - Data is loaded into the target source for the first time.
Full Load - This is the equivalent of a new load process. All existing data is erased and fresh data is loaded into the store.
Incremental Load - Only changes to the target source are made. This is generally a scheduled process.
- What is a factless fact schema?
A factless fact schema is a fact table without measures. That is, the facts represented in the table are likely computations (statistics, for example) and is more akin to a report table.